cancel
Showing results for 
Search instead for 
Did you mean: 

How to Determine Date difference

1,390

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 ?

Accepted Solutions (0)

Answers (2)

Answers (2)

fvestjens
Participant

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
Product and Topic Expert
Product and Topic Expert

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
Participant
0 Kudos

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

VolkerBarth
Contributor
0 Kudos

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

0 Kudos

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