Date and time in SQL statements

Date and time strings in SQL Statements

IBM^® Informix^® allows you to specify date and time values as a quoted character string in a specific format, depending upon DBDATE and GLS environment variables. For example, if DBDATE=DMY4, the following statement specifies a valid DATE represented by a string literal:

SELECT COUNT(*) FROM table WHERE date_col = '24/12/2005'

Other database servers do support date/time literals as quoted character strings, but the date/time format specification is quite different. The parameter to specify the date/time format can be a database parameter, an environment variable, or a session option.

In order to write portable SQL, use SQL parameters instead of string literals for date-time values:

DEFINE cnt INTEGER
DEFINE adate DATE
LET adate = MDY(12,24,2005)
SELECT COUNT(*) INTO cnt FROM table
    WHERE date_col = adate

Or, when using dynamic SQL:

DEFINE cnt INTEGER
DEFINE adate DATE
LET adate = MDY(12,24,2005)
PREPARE s1 FROM "SELECT COUNT(*) FROM table WHERE date_col = ?"
EXECUTE s1 USING adate INTO cnt

Date-time literals

IBM Informix DATETIME and INTERVAL literals are not converted automatically by the SQL translator of the database driver:

SELECT COUNT(*) FROM order WHERE ord_when > DATETIME (1999-10-12) YEAR TO DAY

Check your code, to detect where you are using such expressions in the SQL statements, and use an SQL parameter instead.

Informix-specific keywords

SQL Statements using expressions such as TODAY, CURRENT and EXTEND must be reviewed and adapted to the native syntax of the target database engine.

Check your code, to detect where you are using such expressions in the SQL statements.

Date-time expressions with parameters

Date-time arithmetic expressions using SQL parameters (USING variables) are not portable.

For example:

PREPARE s1 FROM "SELECT ... WHERE datecol < ? + 1"

Might generate an error with non-Informix databases.

DATEs as a number of days

IBM Informix can automatically convert integers to a DATE values, as a number of days since 12/31/1899 ( 1 = 01/01/1900 ). This is however not supported by other database engines.

Check your code, to detect where you are using integers with DATE columns.