How to calculate the difference between two dates with a return value type of integer in Informix?

Question

I am using an Informix database. I want to get the difference between two dates, and the data type of return value must be an integer. The SQL looks like:

select (today - to_date('20121201','%Y%m%d'))  from your_table_name

However, when I execute the SQL it returns 45 and certainly the computed value is not the integer type. What's the date type of the value? How can I cast the value to integer?

Solution

The TO_DATE function is an Oracle import, and returns an Oracle DATE type in Oracle (hence the name), but the Oracle DATE type corresponds to an Informix DATETIME type. So, the Informix implementation of the TO_DATE() function returns a DATETIME type.

Problem

SELECT TODAY AS TODAY,
       TO_DATE('20121201', '%Y%m%d') AS raw_to_date,
       (TODAY - TO_DATE('20121201','%Y%m%d')) AS raw_difference
  FROM sysmaster:informix.sysdual;

today       raw_to_date                   raw_difference
DATE        DATETIME YEAR TO FRACTION(5)  INTERVAL DAY(8) TO DAY
2013-01-16  2012-12-01 00:00:00.00000     46

Solution

The solution for your expression is to apply the DATE function to the output of TO_DATE, or cast it to type DATE.

SELECT DATE(TO_DATE('20121201', '%Y%m%d'))          AS date_to_date,
       CAST(TO_DATE('20121201', '%Y%m%d') AS DATE)  AS cast_to_date,
            TO_DATE('20121201', '%Y%m%d')::DATE     AS colon_to_date,
       (TODAY - DATE(TO_DATE('20121201','%Y%m%d'))) AS date_difference
  FROM sysmaster:informix.sysdual;

date_to_date  cast_to_date  colon_to_date  date_difference
DATE          DATE          DATE           INTEGER
2012-12-01    2012-12-01    2012-12-01     46

Comments

[jason]

Jonathan,I appreciate your help very much.TODAY -DATE(TO_DATE('20121201','%Y%m%d')),the result is integer.

[jason]

Yes,ret,you got it.I find another way like that: select today-DATE('20121201') into ret from tb_dis_controll,and it returns an integer.Thanks very much.