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.

Sybase ASE provides these data type to store dates:

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.

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, Sybase ASE can convert quoted strings representing datetime data in the ANSI format. The CONVERT() SQL function allows you to convert strings to dates.

Date time 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.
Informix automatically converts an integer to a date when the integer is used to set a value of a date column. Sybase ASE does not support this automatic conversion.
Complex DATETIME expressions ( involving INTERVAL values for example ) are Informix specific and have no equivalent in Sybase ASE.
With Sybase ASE you must use built-in functions to do date/time computing (for example, see dateadd() function).
Informix converts automatically an integer to a date when the integer is used to set a value of a date column. Sybase ASE does not support this automatic conversion.

Solution

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

Sybase 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 Sybase 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".

Literal DATETIME and INTERVAL expressions (i.e. DATETIME ( 1999-10-12 ) YEAR TO DAY) are translated by the driver to convert() expressions.

Using integers as a number of days in an expression with dates is not supported by Sybase ASE. Check your code to detect where you are using integers with DATE columns.

It is strongly recommended that you use BDL variables in dynamic SQL statements instead of quoted strings representing DATEs. For example:

LET stmt = "SELECT ... FROM customer WHERE creat_date >'", adate,"'"

is not portable; use a question mark place holder instead and OPEN the cursor USING adate:

LET stmt = "SELECT ... FROM customer WHERE creat_date > ?"