DATE and DATETIME data types

Informix®

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.

The DATE type is stored as an INTEGER with the number of days since 1899/12/31.

The DATETIME type can be defined with various time units, by specifying a start and end qualifier. For example, you can define a datetime to store an hour-to-second time value with DATETIME HOUR TO SECOND.

The values of Informix DATETIME can be represented with a character string literal, or as DATETIME() literals:
'2017-12-24 15:45:12.345'  -- a DATETIME YEAR TO FRACTION(3)
'15:45'   -- a DATETIME HOUR TO MINUTE
DATETIME(2017-12-24 12:45) YEAR TO MINUTE
DATETIME(12:45:56.333) HOUR TO FRACTION(3)
Informix is able to convert quoted strings to DATE / DATETIME data, if the string contains matching environment parameters. The string to date conversion rules for DATE is defined by the DBDATE environment variable. The string to datetime format for DATETIME is defined by the GL_DATETIME environment variable.
Note: Within Genero programs, the string representation for DATETIME values is always ISO (YYYY-MM-DD hh:mm:ss.fffff)

Informix supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is an INTEGER number of days when only DATE values 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.

Microsoft™ SQL Server

Microsoft SQL Server (2008+) provides the following data type to store date and time data:

Table 1. Microsoft SQL Server date/time data types
Microsoft SQL Server data type Description
DATE for year, month, day storage
DATETIME for year, month, day, hour, min, second, fraction(3) storage (from January 1, 1753 through December 31, 9999). Values are rounded to increments of .000, .003, or .007 seconds
SMALLDATETIME for year, month, day, hour, minutes storage (from January 1, 1900, through June 6, 2079). Values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute
TIME(n) for hour, minute, second and fraction(7) storage. Where n defines the precision of fractional seconds
DATETIME2(n) for year, month, day, hour, minute, second and fraction(7) storage. Where n defines the precision of fractional seconds
DATETIMEOFFSET(n) for year, month, day, hour, minute, second, fraction(7) and time zone information storage. Where n defines the precision of fractional seconds

Like Informix, Microsoft SQL Server can convert quoted strings to DATETIME data. The CONVERT() SQL function allows you to convert strings to dates.

Microsoft SQL Server does not allow direct arithmetic operations on datetimes; the date handling SQL functions must be used instead (DATEADD and DATEDIFF).

SQL Server provides equivalent functions for Informix YEAR(), MONTH() and DAY(). Take care with the DAY() function on SQL Server because it begins from January 1, 1900 while Informix begins from December 31, 1899.

Table 2. Select first day example (Informix vs. Microsoft SQL Server)
Informix Microsoft SQL SERVER 
SELECT day(0), month(0), year(0)
  FROM systables WHERE tabid=1;
------ ------ ------
    31     12   1899
 
SELECT day(0), month(0), year(0)
----------- ----------- -----------
          1           1        1900

The SQL Server equivalent for Informix WEEKDAY() is the DATEPART(dw,date-value) function.

The weekday date part depends on the value set by SET DATEFIRST n, which sets the first day of the week (1=Monday ... 7=Sunday (default)).

Solution

Use the following conversion rules to map Informix date/time types to Microsoft SQL Server date/time types:

Table 3. Informix data types and Microsoft SQL Server equivalents
Informix data type Microsoft SQL Server data type
DATE DATE
DATETIME HOUR TO SECOND TIME(0)
DATETIME HOUR TO FRACTION(n) TIME(n)
DATETIME YEAR TO SECOND DATETIME2(0)
DATETIME q1 TO q2 (different from above) DATETIME2(n)
The DATE and DATETIME types translation can be controlled with the following FGLPROFILE entries:
dbi.database.dsname.ifxemul.datatype.date = { true | false }
dbi.database.dsname.ifxemul.datatype.datetime = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.

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:
  • When fetching a TIME or DATETIME2 with a precision that is greater than 5 (the 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.
  • 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 ).
  • Use the SQL Server's GETDATE() function to get the system current date.