How to Convert a DB2 'Date' Datatype to a SQL Server 'DateTime' Dataype? - Page 2

Mr.Bean · #16 (**permalink**) 12-29-11, 10:30

Quote:

Originally Posted by Peter.Vanroose

That will never work!
The whole "Char(...,ISO)" stuff has to go inside the OPENQUERY, since it's DB2 syntax. Outside you need something which is valid SQL Server syntax.

Why not use

Code:

SELECT * FROM OPENQUERY(DB2Server,
'select CustomerID, CHAR(DB2DateColumn, ISO)||'' 00:00:00'', CustAddress
 from DB2Schema.Table')

If you need a nice column name for the second column, add it just before the last comma, e.g.

Code:

SELECT * FROM OPENQUERY(DB2Server,
'select CustomerID,
        CHAR(DB2DateColumn, ISO)||'' 00:00:00'' AS DB2DateColumn,
        CustAddress
 from DB2Schema.Table')

Well when queried as an individual column, it gives me the output. However, if queried with other columns of table, it throws me an error.