|
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 ? |
|
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 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 '22, 18:25)
Chris Keating
Replies hidden
You're right. It might need some adjustments to get it working properly
(29 Sep '22, 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 '22, 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? |
I suggest you post what you've tried so far to get some feedback.