AS400 Date can't be queried from SQL Server

Posted on 2007-10-16
Last Modified: 2013-12-06
working with an AS400 in which date fields default to 1001-01-01 for unknown or null dates.  SQL Server can't query it since it expects dates that begin much later than the middle ages.  Anyone know how to query this type of date file in AS400 without running into SQL error?
Question by:tiote
    LVL 30

    Accepted Solution

    Does it try to convert it to a date at the ODBC driver level?

    If you could leave it as a string then covert it on the fly at the SQL Server side it would be OK.


    Author Comment

    The problem seems to be at the ODBC level. Consider this statement:
    Select <some date field> from Openquery(AS400,'Select <some date field> from <some table>.<some member>')
    What occurs is that SQL OPENQUERY attempts to return the date field from the OPENQUERY statement by first validating that the field is a date. So even if I try to convert or cast the field in the sql statement as in :
    Select CONVERT(VARCHAR(20),<some date field>) from Openquery(AS400,'Select <some date field> from <some table>.<some member>')
    It fails on attempting to validate the date field before the CONVERT function is used.  So the question is:  How do I cast or convert the date field inside the  Openquery(AS400,'Select <some date field> from <some table>.<some member>') statement?

    The date fields that fail are always in the range of '1001-01-01' or '0001-01-01'.  SQL Server gags on these in the fashion I describe above.  These are the default values for null dates.

    Hope this makes sense.  Anyone got any ideas?
    LVL 14

    Expert Comment

    I have done some testing and have hit a wall. The only thing I can think of is creating a image updating trigger that sets the date to a SQL server compatible date (ie 1900-01-01 ) , then update the DB to a date ie 1900-01-01 so sql can access it.

    Computers Eh !!!!

    LVL 30

    Expert Comment

    What about if you cast it in the DB2 pass through query first:

    Select <some date field> from Openquery(AS400,'Select CHAR(somedatefield) As adatefield from <some table>.<some member>')

    That will let it through the ODBC driver. Then you convert in in a case statement:

    Select CASE WHEN ISDATE(adatefield) THEN CAST(adatefield as DATETIME) ELSE '1900-01-01' END
    Openquery(AS400,'Select CHAR(somedatefield) As adatefield from <some table>.<some member>')

    Author Comment

    Well nmcdermaid, All I can say to that is: 'u da man'.  

    I diddled slightly with your statement and came up with this:
    SELECT CASE WHEN ISDATE(<Some Field>) = 1 THEN CAST(<Some Field> AS DATETIME) ELSE '1900-01-01'  END AS <Some Field> FROM OPENQUERY(AS400*, 'SELECT CHAR(<Some Field>)  AS <Some Field> FROM <Some Table File>.<Some Member>')

    * Where AS400 is the named of the Linked Server in SQL Server

    The result:
    No errors, the valid dates are there and the '0001-01-01' dates are converted!

    If you are anywhere near Philly let me know because I owe you a beer.  Thanks again.
    LVL 30

    Expert Comment

    hehe thanks. If I'm anywhere near Philly I will!!!... I'm in Australia now but who knows what the future will bring.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
    I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension ( This reminded me of questions tha…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now