DATE and DATETIME data types

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

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

Netezza^® provides the following data type to store date and time information:

DATE = for year, month, day storage.
TIME = for hour, minute, second, fraction with (6 decimal positions).
TIME WITH TIME ZONE / TIMETZ = same as TIME, with time zone information.
TIMESTAMP = for year, month, day, hour, minute, second, fraction (with 6 decimal positions).

String representing date time information

Informix is able to convert quoted strings to DATE / DATETIME data if the string contents matches environment parameters (i.e. DBDATE, GL_DATETIME). As in Informix, Netezza can convert quoted strings to date time data. Netezza accepts different date formats, including ISO date time strings, and you can specify the cast operator (::date, ::time, ::timestamp) after the string literal.

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 Netezza, the result of an arithmetic expression involving DATE values is an INTEGER representing a number of days.
Informix automatically converts an integer to a date when the integer is used to set a value of a date column. Netezza does not support this automatic conversion.
Complex DATETIME expressions ( involving INTERVAL values for example) are Informix specific and have no equivalent in Netezza.

Using DATE/DATETIME variables in SQL statements

Informix supports implicit DATE/DATETIME conversions, for example you can use a DATE variable when the target column is a DATETIME. This is not possible with Netezza: The type of the SQL parameter must match the type of the column in the database table.

Solution

Netezza has the same DATE data type as Informix ( year, month, day ). So you can use Netezza DATE data type for Informix DATE columns.

Netezza TIME data type can be used to store Informix DATETIME HOUR TO SECOND values. The database interface makes the conversion automatically.

Informix DATETIME values with any precision from YEAR to FRACTION(5) can be stored in Netezza TIMESTAMP columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00.0. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the Netezza TIMESTAMP value will be "1900-01-01 11:45:00.0".

Note:

Make sure that you are using the same type for the SQL parameter and the target column, DATE/DATETIME implicit conversion is not supported by Netezza.

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