DATE and DATETIME data types

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

DATE = for year, month and day storage.
DATETIME = for year to fraction(1-5) storage.

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

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.

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

DATE = for year, month, day storage as Informix DATEs.
TIME(n) = for hour, minute, second and fraction(7) storage. Here n defines the precision of fractional seconds.
DATETIME2(n) = for year, month, day, hour, minute, second and fraction(7) storage. Here n defines the precision of fractional seconds.
DATETIMEOFFSET(n) = for year, month, day, hour, minute, second, fraction(7) and time zone information storage. Here n defines the precision of fractional seconds.

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

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.
Informix automatically converts an integer to a date when the integer is used to set a value of a date column. Microsoft SQL SERVER does not support this automatic conversion.
Complex DATETIME expressions ( involving INTERVAL values for example) are Informix specific and have no equivalent in Microsoft SQL SERVER.
Microsoft SQL SERVER does not allow direct arithmetic operations on datetimes; the date handling SQL functions must be used instead (DATEADD & DATEDIFF).

The SQL SERVER provides equivalent functions for YEAR(), MONTH() and DAY(). Be careful with the DAY(n) function on SQL SERVER because it begins from January 1, 1900 while Informix begins from December 31, 1899.

Table 1. 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 WEEKDAY() is the DATEPART(dw,<date>) 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)).
SQL SERVER uses a different basis for the day of the week. In SQL SERVER, Sunday is day 7 and Monday is day 1 while Informix defines Sunday as the day 0 (zero) and Monday as 1.

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 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.

See also Date and time in SQL statements for good SQL programming practices.