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