SQL Server – Datetime date range , AS400 and OPENQUERY

16 October,2012 by Tom Collins

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.

Some  workarounds:

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

Related Posts

SQL Server Import Data from DB2 AS400 iSeries

SQL Server - How to setup ODBC for IBM AS/400

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – Datetime date range , AS400 and OPENQUERY

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer