DATE and DATETIME data types

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

IBM® DB2® provides only one data type to store dates:

String representing date time information

Informix is able to convert quoted strings to DATE / DATETIME data if the string content matches environment parameters (i.e. DBDATE, GL_DATETIME). As Informix, IBM DB2 can convert quoted strings to dates, times or timestamps. Only one format is possible: 'yyyy-mm-dd' for dates, 'hh:mm:ss' for times and'yyyy-mm-dd hh:mm:ss:f' for timestamps.

Date time arithmetic

Solution

DB2 has the same DATE data type as Informix ( year, MIX DATE columns.

DB2 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 DB2 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 DB2 TIMESTAMP value will be "1900-01-01 11:45:00.0".

Important:
  • Using integers as a number of days in an expression with dates is not supported by IBM DB2. Check your code to detect where you are using integers with DATE columns.
  • Literal DATETIME and INTERVAL expressions (i.e. DATETIME ( 1999-10-12) YEAR TO DAY) are not converted.
  • 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 > ?"

  • DATE arithmetic expressions using SQL parameters (USING variables) are not fully supported. For example:

    "SELECT ... WHERE datecol < ? +1" generates an error at PREPARE time.

  • SQL Statements using expressions with TODAY / CURRENT / EXTEND must be reviewed and adapted to the native syntax.