Question Re: How to Determine Date difference in Technology Q&A

How to Determine Date difference

former_SQLA_member1695027 — Sat, 24 Sep 2022 08:17:22 GMT

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 ?

Re: How to Determine Date difference

justin_willey — Sat, 24 Sep 2022 12:41:40 GMT

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

Re: How to Determine Date difference

former_SQLA_member1694901 — Sun, 25 Sep 2022 11:00:51 GMT

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

Re: How to Determine Date difference

fvestjens — Mon, 26 Sep 2022 03:04:08 GMT

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

Re: How to Determine Date difference

chris_keating — Wed, 28 Sep 2022 18:25:43 GMT

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.

Re: How to Determine Date difference

fvestjens — Thu, 29 Sep 2022 02:10:52 GMT

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

Re: How to Determine Date difference

VolkerBarth — Thu, 29 Sep 2022 04:18:16 GMT

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