DATE and DATETIME data types

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

Microsoft™ SQL SERVER provides two data type to store dates:

Starting with Microsoft SQL SERVER 2008, following new date data types are available:

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, Microsoft SQL SERVER can convert quoted strings to DATETIME data. The CONVERT() SQL function allows you to convert strings to dates.

Date time arithmetic

Solution

The SQL SERVER database drivers will automatically map Informix date/time types to native SQL SERVER type, according the server version. Conversions are described in this table:

Table 2. Date/time mapping between Informix and Microsoft SQL Server
Informix date/time type Microsoft SQL SERVER date/time type before SQL SERVER 2008 Microsoft SQL SERVER date/time type since SQL SERVER 2008
DATE DATETIME DATE
DATETIME HOUR TO SECOND DATETIME (filled with 1900-01-01) TIME(0)
DATETIME HOUR TO FRACTION(n) DATETIME (filled with 1900-01-01) TIME(n)
DATETIME YEAR TO SECOND DATETIME DATETIME2(0)
Any other sort of DATETIME type DATETIME (filled with 1900-01-01) DATETIME2(n)

With SQL SERVER 2005 and lower, Informix DATETIME with any precision from YEAR to FRACTION(3) is stored in SQL SERVER DATETIME columns.

For heterogeneous DATETIME types like DATETIME HOUR TO MINUTE, the database interface fills missing date or time parts to 1900-01-01 00:00:00.0. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the SQL SERVER datetime value will be "1900-01-01 11:45:00.0".

Important:
  • SQL SERVER SMALLDATETIME can store dates from January 1, 1900, through June 6, 2079. Therefore, we do not recommend using this data type.
  • With SQL SERVER 2005 and lower, the fractional second part of a SQL SERVER DATETIME has a precision of 3 digits while Informix has a precision up to 5 digits. Do not try to insert a datetime value in a SQL SERVER DATETIME with a precision more than 3 digits or a conversion error could occur. You can use the MS SUBSTRING() function to truncate the fraction part of the Informix datetimes or another BDL solution. The fraction part of a SQL SERVER DATETIME is an approximate value. For example, when you insert a datetime value with a fraction of 111, the database actually stores 110. This may cause problems because Informix DATETIMEs with a fraction part are exact values with a precision up to 5 digits. Starting with SQL SERVER 2008, the DATETIME2 native type will be used. This new type can store fraction of seconds with a precision of 7 digits, so Informix DATETIME values can be stored without precision lost.
  • When migrating to SQL SERVER 2008, you must pay attention if the database has DATETIME columns used to store Informix DATETIME HOUR TO SECOND or DATETIME HOUR TO FRACTION(n) types: Before version 2008, those types were stored in SQL SERVER DATETIME columns (filling missing date part with 1900-01-01). The SNC database driver for SQL SERVER 2008 maps now DATETIME HOUR TO SECOND / FRACTION(n) to a TIME data type, which is not compatible with an SQL SERVER DATETIME type. To solve this problem, SQL SERVER DATETIME columns used to store DATETIME HOUR TO SECOND/FRACTION(n) must be converted to TIME columns (ALTER TABLE).
  • When fetching a TIME or DATETIME2 with a precision that is greater as 5 (the 4gl DATETIME precision limit), the database interface will allocate a buffer of VARCHAR(16) for the TIME and VARCHAR(27) for the DATETIME2 column. As a result, you can fetch such data into a CHAR or VARCHAR variable.
  • Using integers as a number of days in an expression with dates is not supported by SQL SERVER. 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. Review your code for occurrences
  • It is strongly recommended to 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 a date:

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

  • Review the program logic if you are using the Informix WEEKDAY() function because SQL SERVER uses a different basis for the days numbers ( Monday = 1 ).
  • SQL Statements using expressions with TODAY / CURRENT / EXTEND must be reviewed and adapted to the native syntax. Use the MS GETDATE() function to get the system current date.