I have two dates of the structure Start Date: 2007-03-24, End Date: 2009-06-26

Presently I want to track down the difference between these two in the beneath structure:

2 years, 90 days, and 2 days

I Have gone through the procedure described here but I'm a Little bit scared if I tried wrong. Can someone give some insight here ?

asked 24 Sep, 08:17

sarthakjain's gravatar image

sarthakjain
3114
accept rate: 0%

1

I suggest you post what you've tried so far to get some feedback.

(24 Sep, 12:41) Justin Willey

Try this:

create or replace function DateDifference(in in_StartDate date,in in_EndDate date)
returns varchar(64)
begin
 declare l_Years integer;
 declare l_Months integer;
 declare l_Days integer;
 declare l_Date date;

 select datediff(year,in_StartDate,in_Enddate) into l_Years;

 set l_Date = dateAdd(year,l_years,in_startDate);
 select datediff(month,l_Date,in_EndDate) into l_Months;

 set l_Date = dateAdd(month,l_months,l_Date);
 select datediff(day,l_Date,in_EndDate) into l_Days

 return (if l_Years > 0 then l_Years ||' years ' endif) ||
        (if l_Months > 0 then l_Months ||' months' endif) ||
        (if l_Days > 0 then ' and '|| l_Days ||' days' endif) 
end

select DateDifference('2007-03-24','2009-06-26') will return 2 years, 3 months and 2 days

permanent link

answered 26 Sep, 03:04

Frank%20Vestjens's gravatar image

Frank Vestjens
1.0k283851
accept rate: 25%

2

Note that this will not work correctly for all dates.

If the second date has a lower value for either MONTH or DAY, it will ignore the MONTH and/or DAY. DateDifference('2022-11-30','2022-12-01') // returns 1 month DateDifference('2022-12-31','2023-01-01') // returns 1 year

Both of these should be 1 day.

(28 Sep, 18:25) Chris Keating
Replies hidden

You're right. It might need some adjustments to get it working properly

(29 Sep, 02:10) Frank Vestjens

It would probably be easy to swap dates internally if StartDate is before EndDate and "negate" the result then.

(29 Sep, 04:18) Volker Barth

It's hard to say what eyactly you need, have you tried casting the strings to DATE and using the DAYS function of Anywhere?

permanent link

answered 25 Sep, 11:00

tedfroehlich's gravatar image

tedfroehlich
2302516
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×38
×5
×4

question asked: 24 Sep, 08:17

question was seen: 195 times

last updated: 29 Sep, 04:18