DATE and DATETIME data types

Informix®

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.

The DATE type is stored as an INTEGER with the number of days since 1899/12/31.

The DATETIME type can be defined with various time units, by specifying a start and end qualifier. For example, you can define a datetime to store an hour-to-second time value with DATETIME HOUR TO SECOND.

The values of Informix DATETIME can be represented with a character string literal, or as DATETIME() literals:

'2017-12-24 15:45:12.345'  -- a DATETIME YEAR TO FRACTION(3)
'15:45'   -- a DATETIME HOUR TO MINUTE
DATETIME(2017-12-24 12:45) YEAR TO MINUTE
DATETIME(12:45:56.333) HOUR TO FRACTION(3)

Informix is able to convert quoted strings to DATE / DATETIME data, if the string contains matching environment parameters. The string to date conversion rules for DATE is defined by the DBDATE environment variable. The string to datetime format for DATETIME is defined by the GL_DATETIME environment variable.

Note: Within Genero programs, the string representation for DATETIME values is always ISO (YYYY-MM-DD hh:mm:ss.fffff)

Informix supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is an INTEGER number of days when only DATE values are used, and an INTERVAL value if a DATETIME is used in the expression.

Informix automatically converts an INTEGER to a DATE when the integer is used to set a value of a date column.

SAP ASE

SAP® ASE provides the following data type to store date and time data:

Table 1. SAP ASE date/time data types
SAP ASE SERVER data type	Description
`DATE`	for year, month, day storage
`TIME`	for hour, minutes, seconds, fraction(3) storage
`SMALLDATETIME`	for hour, minutes, seconds, fraction(3) storage
`DATETIME`	for hour, minutes, seconds, fraction(3) storage
`BIGTIME`	for hour, minutes, seconds, fraction(6) storage
`BIGDATETIME`	for year, month, day, hour, minutes, seconds, fraction(6) storage

SAP ASE can convert quoted strings representing datetime data in the ANSI format. The CONVERT() SQL function allows you to convert strings to dates.

With SAP ASE, you must use built-in functions to do date/time computing (for example, see dateadd() function).

Solution

Use the following conversion rules to map Informix date/time types to SAP ASE date/time types:

Table 2. Informix data types and SAP ASE equivalents
Informix data type	SAP ASE data type
`DATE`	`DATE` (yyyy-mm-dd)
`DATETIME HOUR TO FRACTION(n)`	`BIGTIME` (hh:mm:ss.ffffff)
`DATETIME HOUR TO SECOND`	`BIGTIME` (hh:mm:ss.ffffff)
`DATETIME q1 TO q2` (different from above)	`BIGDATETIME`(yyyy-mm-dd hh:mm:ss.ffffff)

The DATE and DATETIME types translation can be controlled with the following FGLPROFILE entries:

dbi.database.dsname.ifxemul.datatype.date = { true | false }
dbi.database.dsname.ifxemul.datatype.datetime = { true | false }

For more details see IBM Informix emulation parameters in FGLPROFILE.

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

SAP ASE BIGTIME data type can be used to store Informix DATETIME HOUR TO SECOND and DATETIME HOUR TO FRACTION(5) values. The database interface makes the conversion automatically.

Informix DATETIME values with any precision from YEAR to FRACTION(5) can be stored in SAP ASE BIGDATETIME 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 ASE TIMESTAMP value will be "1900-01-01 11:45:00.0".

Note: SAP ASE does not support INTEGER to DATE automatic conversion.