Informix SQL - Number of days between 2 dates

Question

I was trying to get the number of days between 2 datetimes (timestamp value with time as 00:00:00) and tried the following query:

select
   "Date1",
   "Date2",
   extend("Date1", year to fraction(5)) - extend("Date2", year to fraction(5)) as "TotalDaysDifference" 
from
   "mytable"

This query returns the values in the following manner:

If Day1 = '2021-02-29 00:00:00' and Day2 = '2021-09-27 00:00:00', then TotalDaysDifference = '7881 00:00:00.00000'
If Day1 = '1900-12-31 00:00:00' and Day2 = '2021-09-27 00:00:00', then TotalDaysDifference = '44100 00:00:00.00000'

I just need the 7881 and 44100 values out of it. How do I go about extracting that out of this interval?

Luís Marques · Accepted Answer · 2021-10-04T16:34:39.363

What version of Informix are you using?

Here is an example using Informix 14.10.FC5 :

CREATE TABLE mytable
(
  id    INTEGER,
  date1 DATETIME YEAR TO SECOND,
  date2 DATETIME YEAR TO SECOND
);
-- Using 2021-02-28 instead of 2021-02-29 because 2021-02-29 is an incorrect date and Informix returns an error.
INSERT INTO mytable VALUES ( 1, '2021-02-28 00:00:00', '2021-09-27 00:00:00' );
INSERT INTO mytable VALUES ( 2, '1900-12-31 00:00:00', '2021-09-27 00:00:00' );

SELECT
  date1,
  date2,
  ( date1 - date2 )::INTERVAL DAY(9) TO DAY AS daysdifference1, -- no need to transform the datetime with "extend". I just cast the result to an interval of days
  ( date2 - date1 )::INTERVAL DAY(9) TO DAY AS daysdifference2
FROM
  mytable
WHERE
  id = 1
;

date1               date2               daysdifference1 daysdifference2

2021-02-28 00:00:00 2021-09-27 00:00:00       -211             211

SELECT
  date1,
  date2,
  ( date1 - date2 )::INTERVAL DAY(9) TO DAY AS daysdifference1, -- no need to transform the datetime with "extend". I just cast the result to an interval of days
  ( date2 - date1 )::INTERVAL DAY(9) TO DAY AS daysdifference2
FROM
  mytable
WHERE
  id = 2
;

date1               date2               daysdifference1 daysdifference2

1900-12-31 00:00:00 2021-09-27 00:00:00     -44100           44100

This is what I really needed for my use case. Thanks a lot! – Nick Oct 04 '21 at 16:12 — Nick, Oct 04 '21 at 16:12

score 1 · Answer 2 · answered Oct 04 '21 at 03:57

If you're not concerned about the times in the two date/time values, then simply convert to DATE values and subtract:

SELECT "Date1", "Date2",
       "Date1"::DATE, "Date2"::DATE,
       "Date2"::DATE - "Date1"::DATE
  FROM "mytable"

You must be running with DELIMIDENT set in the environment to enable delimited identifiers enclosed in double quotes.

The advantage of converting to DATE is that the result of the subtraction is an integer value, not an interval, and that is usually simpler to use.

Your first example is:

If Day1 = '2021-02-29 00:00:00' and Day2 = '2021-09-27 00:00:00', then TotalDaysDifference = '7881 00:00:00.00000'.

The value should be for Date1 according to your SQL. And since 2021 was not a leap year, you should not be able to insert the value 2021-02-29. Further, there are but 365 days in a year, so it is not clear why you expect there to be 7,881 days between two dates in the same year.

However, if we change 2021-02-29 to 2000-02-29 (the year 2000 was a leap year!), then the result of the subtraction is indeed 7881.

Proof of concept

CREATE TEMP TABLE "mytable"
(
    "Date1" DATETIME YEAR TO SECOND NOT NULL,
    "Date2" DATETIME YEAR TO SECOND NOT NULL
);

INSERT INTO "mytable" VALUES('1900-12-31 00:00:00', '2021-09-27 00:00:00');
--INSERT INTO "mytable" VALUES('2021-02-29 00:00:00', '2021-09-27 00:00:00');
INSERT INTO "mytable" VALUES('2000-02-29 00:00:00', '2021-09-27 00:00:00');

SELECT "Date1", "Date2",
       "Date1"::DATE AS "Date1 AS DATE",
       "Date2"::DATE AS "Date1 AS DATE",
       "Date2"::DATE - "Date1"::DATE AS "Difference in days"
  FROM "mytable";

Output

I use DBDATE=Y4MD- in the environment, so DATE values are formatted like DATETIME values.

Date1	Date2	Date1 AS DATE	Date1 AS DATE	Difference in days
DATETIME YEAR TO SECOND	DATETIME YEAR TO SECOND	DATE	DATE	INTEGER
1900-12-31 00:00:00	2021-09-27 00:00:00	1900-12-31	2021-09-27	44100
2000-02-29 00:00:00	2021-09-27 00:00:00	2000-02-29	2021-09-27	7881

Thank you very much for the the detailed insight! Very helpful @Jonathan Leffler — Nick, Oct 04 '21 at 16:06

Informix SQL - Number of days between 2 dates

2 Answers2

Proof of concept

Output