How to Determine Date difference

[former_SQLA_member1695027]

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 ?

[justin_willey]

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

[fvestjens]

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

[chris_keating]

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.

[fvestjens]

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

[VolkerBarth]

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

[former_SQLA_member1694901]

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