SQL date format

[shaminda]

I am trying to query a table in AS400, the date field in the AS400 is text(6). So when I try to query from Query analyzer I get the following error message.

Error converting data type DBTYPE_DBDATE to datetime.

How do I convert the date field to datetime with T-SQL? Here is the query I am trying to run:

SELECT CPART, REQDAT, RANNO, VIN#
FROM OPENQUERY(AS400, 'SELECT CPART, REQDAT, RANNO, VIN# FROM FUTPRNT.RSPBPHST')

Here is an example of REQDAT the way it appears in AS400: 3/11/2004

 

[SQLBill]

Start your SQL Server script with:

SET DATEFORMAT mdy

That let's SQL Server know how to interpret the date information. Otherwise 3/11/04 could be:

March 11 2004
3 November 2004
2003 November 04

-SQLBill