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.
IBM® DB2® provides only one data type to store dates:
- DATE = for year, month, day storage.
- TIME = for hour, minute, second storage.
- TIMESTAMP = for year, month, day, hour, minute, second,
fraction storage.
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
- 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 IBM
DB2, the result of an arithmetic expression involving DATE
values is a NUMBER of days, the decimal part is the fraction of the day ( 0.5 = 12H00, 2.00694444 =
(2 + (10/1440)) = 2 days and 10 minutes ) ).
- Informix automatically converts an integer to a date
when the integer is used to set a value of a date column. IBM
DB2 does not support this automatic conversion.
- Complex DATETIME expressions ( involving INTERVAL values for example) are Informix specific and have no equivalent in IBM
DB2.
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.