16 October,2012 by Jack Vamvas
I executed an OPENQUERY statement onto a Linked Server pointing to an AS400 database. For the datetime data types some problems can occur, if the dates are out of range.
The SQL Server Datetime function presents a date. The date is combined with the time based on 24 hrs clock. One of its characteristics is the Date Range. The Date Range according to BOL is January 1, 1753, through December 31, 9999.
For the YYYY element (representing the year ) of the datetime – this means a range of 1753 to 9999 .
One of the consequences is running an OpenQuery onto a AS400 – is the use of ISO 8601 date '0001-01-01'. The '0001-01-01' is acceptable to the iSeries, but will throw an error on SQL Server. The It is not uncommon for a AS400 developer to program for a 0001 year.The date data type columns on AS400 map to datetime data type in SQL Server. This causes an exception when the data range is out of 1753 - 9999
In SQL Server 2008 – the date functions complies with the ANSI SQL standard definition for the Gregorian calendar: "NOTE 85 - Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001–01–01 CE through 9999–12–31 CE."
This gives some options to selecting AS400 date columns through OPENQUERY. You can either deal with it programmatically or request the date is dealt with at source.
1) Change the ‘0001’ type data to NULL at AS400
2) Import into a SQL Server table as VARCHAR
3) Use CASE statement to deal with 01/01/0001