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?

CodePudding user response：

Can you not use the between function?

Where DateColumn BETWEEN '2021-01-01' and '2021-02-02'

Also, if you want to add them If date1 = X and date2 = Y then XY

I think it would be easiest using a CASE THEN statement.

These will give you the answer:

Informix - BETWEEN two dates

CASE statement based on multiple rows

CodePudding user response：

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 do 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 do 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