tiote
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi
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 !!!!
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 !!!!
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
from
Openquery(AS400,'Select CHAR(somedatefield) As adatefield from <some table>.<some member>')
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
from
Openquery(AS400,'Select CHAR(somedatefield) As adatefield from <some table>.<some member>')
ASKER
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.
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.
ASKER
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?