• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1715
  • Last Modified:

AS400 Date can't be queried from SQL Server

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?
  • 3
  • 2
1 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.

tioteAuthor Commented:
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?
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 !!!!

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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>')
tioteAuthor Commented:
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.
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now