DATE and DATETIME data types

Informix^® provides two data types to store date and time information:

DATE = for year, month and day storage.
DATETIME = for year to fraction(1-5) storage.

ORACLE provides only the following data types to store date and time data:

DATE = for year, month, day, hour, min, second storage.
TIMESTAMP= for year, month, day, hour, min, second, fraction storage.

String representing date time information

Informix is able to convert quoted strings to DATE / DATETIME data if the string contains matching environment parameters (i.e. DBDATE, GL_DATETIME).

As in Informix, ORACLE can convert quoted strings to DATE or TIMESTAMP data if the contents of the string matches the NLS date format parameters (NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT). The TO_DATE() and TO_TIMESTAMP() SQL functions convert strings to dates or timestamps, according to a given format. The TO_CHAR() SQL function allows you to convert dates or timestamps to strings, according to a given format.

Date arithmetic

Informix supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is a number of days when only DATEs are used, and an INTERVAL value if a DATETIME is used in the expression. In ORACLE, the result of an arithmetic expression involving DATE values is a NUMBER of days; the decimal part is the fraction of the day ( 0.5 = 12H00, 2.00694444 = (2 + (10/1440)) = 2 days and 10 minutes ). The result of an expression involving Oracle TIMESTAMP data is of type INTERVAL. See the Oracle documentation for more details.
Informix automatically converts an integer to a date when the integer is used to set a value of a date column. ORACLE does not support this automatic conversion.
Complex DATETIME expressions ( involving INTERVAL values for example) are Informix-specific and have no equivalent in ORACLE.
To compare dates that have time data in ORACLE, you can use the ROUND() or TRUNC() SQL functions.

Solution

Storing BDL DATE values

The ORACLE DATE type is used to store Genero BDL DATE values. However, keep in mind that the ORACLE DATE type stores also time (hh:mm:ss) information. The database interface automatically sets the time part to midnight (00:00:00) during input/output operations.

You must be very careful since manual modifications of the database might set the time part, for example:

UPDATE table SET date_col = SYSDATE

(SYSDATE is equivalent to CURRENT YEAR TO SECOND in Informix).

After this type of update, when columns have date values with a time part different from midnight, some SELECT statements might not return all the expected rows.

When fetching ORACLE DATE values into Genero BDL DATE or DATETIME variables, the date and time information is directly set for the individual date/time parts and the conversion is straight forward. But when fetching an ORACLE DATE into a CHAR or VARCHAR variable, date to string conversion occurs. Since ORACLE DATEs are equivalent of Informix DATETIME YEAR TO SECOND, the values are by default converted with the ISO format (YYYY-MM-DD hh:mm:ss), which is not the typical Informix behavior where DATEs are formatted according to the DBDATE environment variable. If your application fetches DATE values into CHAR/VARCHAR and you want to get the DBDATE conversion, you must set the following FGLPROFILE entry:

dbi.database.dbname.ora.date.ifxfetch = true

Storing BDL DATETIME values

Informix DATETIME data with any precision from YEAR to SECOND is stored in ORACLE DATE columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the ORACLE DATE value will be "1900-01-01 11:45:00".

Informix DATETIME YEAR TO FRACTION(n) data is stored in ORACLE TIMESTAMP columns. The TIMESTAMP data type can store up to 9 digits in the fractional part, and therefore can store all precisions of Informix DATETIME.

Important:

Most arithmetic expressions involving dates ( for example, to add or remove a number of days from a date ) will produce the same result with ORACLE. But keep in mind that ORACLE evaluates date arithmetic expressions to NUMBERs ( days.fraction ) while Informix evaluates to INTEGERs when only DATEs are used in the expression, or to INTERVALs if at least one DATETIME is used in the expression.
Even if a configuration parameter exists to get the Informix behavior, avoid to fetch date values into CHAR or VARCHAR, to bypass the DBDATE / ISO format conversion difference with ORACLE.

See also Date and time in SQL statements for good SQL programming practices.