How to get the difference between two dates in years, months and days?

Question

I have 2 date values in my FB3 table.

STARTDATE = 09/01/2021
ENDDATE =   05/01/2023

I need to get difference between the 2 dates as YEAR, MONTH, DAY According to above dates, the result = YEAR = 1, MONTH = 8, DAYS = 0

I tried

SELECT EXTRACT(YEAR FROM ENDDATE-STARTDATE),
        EXTRACT(MONTH FROM ENDDATE-STARTDATE),
        EXTRACT(DAY FROM ENDDATE-STARTDATE),

but I get the error message:

Specified EXTRACT part does not exist in input datatype

How can I do it?

What is the result for: `SELECT STARTDATE`? It might just be: `0.00445324`, which is 9 divided by 1 divided by 2021, or you do get `08/01/2021'`, which might be a string, but is not a date value .... First you need to make sure that STARTDATE (and ENDDATE) do contain a DATE, an not something else. — Luuk, Apr 09 '23 at 14:58
Thank you Luuk, SELECT STARTDATE returns 09/01/2021 as DATE same for ENDDATE — Henry Olive, Apr 09 '23 at 15:03
then you might need: [DATEDIFF](https://firebirdsql.org/refdocs/langrefupd21-intfunc-datediff.html) — Luuk, Apr 09 '23 at 15:06
The expression `ENDDATE-STARTDATE` is not a date, time or a timestamp type, so extract won't work. — Mark Rotteveel, Apr 10 '23 at 07:46
@Luuk You link to the Firebird 2.1 Language Reference Update, the 'better' link is the Firebird 3.0 Language Reference: [`DATEDIFF`](https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-scalarfuncs-datediff) — Mark Rotteveel, Apr 10 '23 at 07:49

score 1 · Answer 1 · answered Apr 10 '23 at 08:19

The expression ENDDATE-STARTDATE is not a date, time or a timestamp type, it is an INTEGER with the number of days between the dates, so EXTRACT won't work. Firebird does not have an INTERVAL datatype, like some other DBMSes have.

As Luuk mentioned in the comments, you could use DATEDIFF. However, doing so will not have the desired effect, because DATEDIFF will take the difference between the current unit, ignoring 'lower' units, and it will take all units, so the result would by 2 years (2023-2021 = 2), 20 months (total number of months between September 2021 and May 2023 and 607 days (total number of days between 1 Sept 2021 and 1 May 2023).

There is no built-in way to get the difference you want. You would need to find a custom UDF or UDR that does this, or write your own UDF, UDR or PSQL function or procedure.

For example, something like:

create procedure custom_diff(startdate date, enddate date)
    returns (years integer, months integer, days integer)
as
    declare calcdate date;
begin
    if (startdate > enddate) then
    begin
        calcdate = enddate;
        enddate = startdate;
        startdate = calcdate;
    end
    else
    begin
        calcdate = startdate;
    end
    
    years = datediff(year, calcdate, enddate);
    calcdate = dateadd(year, years, calcdate);
    if (calcdate > enddate) then
    begin
        years = years - 1;
        calcdate = dateadd(year, -1, calcdate);
    end
    
    months = datediff(month, calcdate, enddate);
    calcdate = dateadd(month, months, calcdate);
    if (calcdate > enddate) then
    begin
        months = months - 1;
        calcdate = dateadd(month, -1, calcdate);
    end
    
    days = datediff(day, calcdate, enddate);
    
    suspend;
end

Which you can then use as:

select *
from (
    select date'09/01/2021' as startdate, date'05/01/2023' as enddate 
    from rdb$database
) r
cross join custom_diff(r.startdate, r.enddate)

Which results in :

STARTDATE	ENDDATE	YEARS	MONTHS	DAYS
2021-09-01	2023-05-01	1	8	0

score 1 · Answer 2 · answered Apr 11 '23 at 15:49

Other answers are somehow complicated. A year is reliably 12 months so I would suggest this:

create procedure custom_diff(startdate date, enddate date)
    returns (years integer, months integer, days integer)
as
begin
  months = datediff(month, startdate, enddate);
  if (dateadd(month, months, startdate) > enddate) then
   begin
     months = months - 1;
   end
  years = months / 12;
  days = enddate - dateadd(month, months, startdate);
  months = mod(months, 12);
  suspend;
end

rstrelba · Answer 3 · 2023-04-11T10:37:39.073

create or alter procedure GET_DATE_DIFF (
    I_DATE1 date,
    I_DATE2 date)
returns (
    O_YEARS integer,
    O_MONTHS integer,
    O_DAYS integer)
as
declare variable FSELECTOR smallint;
declare variable V_DATE date;
begin
    O_YEARS = 0;
    O_MONTHS = 0;
    O_DAYS = 0;
    V_DATE = I_DATE1;
    FSELECTOR = 1;/* years */
    while (V_DATE < I_DATE2) do
    begin
        if (FSELECTOR = 1) then
            if (dateadd(year, 1, V_DATE) <= I_DATE2) then
            begin
                V_DATE = dateadd(year, 1, V_DATE);
                O_YEARS = O_YEARS + 1;
            end
            else
                FSELECTOR = FSELECTOR + 1;

        if (FSELECTOR = 2) then
            if (dateadd(month, 1, V_DATE) <= I_DATE2) then
            begin
                V_DATE = dateadd(month, 1, V_DATE);
                O_MONTHS = O_MONTHS + 1;
            end
            else
                FSELECTOR = FSELECTOR + 1;

        if (FSELECTOR = 3) then
        begin
            O_DAYS = datediff(day, V_DATE, I_DATE2);
            V_DATE = I_DATE2;
        end

    end
    suspend;
end

Thank you so much user13964273, rstrelba – Henry Olive Apr 16 '23 at 07:37 — Henry Olive, Apr 16 '23 at 07:37

How to get the difference between two dates in years, months and days?

3 Answers3