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

Wim · #16 (**permalink**) 12-27-11, 14:30

Quote:

Originally Posted by aflorin27

DB2 error messages are not so "user-friendly" as the SQL Server ones. But you may find some useful clues when searching on the Internet the SQLSTATE error codes.

Google eg. "db2 SQLSTATE: 42605, SQLCODE: -170" for a more elaborate error messages.

There is a way to tell DB2, in the connect string, to do the lookup for you and give a more verbose error message, but I forgot how to do it. You may want to post that question on the DB2 forum.

aflorin27 · #17 (**permalink**) 12-28-11, 04:17

Quote:

Originally Posted by Mr.Bean

I've tried it too but it throwed me the same error:

Invalid Object Name

This is strange - sysibm.sysdummy1 should have SELECT privilege granted to PUBLIC. Anyway, the error message tells me that the DB2 connection works just fine.

So, according to this: Data Type Mapping with Distributed Queries this should work:

SELECT CAST(DateColumn AS DATETIME) FROM OPENQUERY(DB2Server, 'select DB2DateColumn from DB2Schema.Table')

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

Quote:

Originally Posted by aflorin27

This is strange - sysibm.sysdummy1 should have SELECT privilege granted to PUBLIC. Anyway, the error message tells me that the DB2 connection works just fine.

So, according to this: Data Type Mapping with Distributed Queries this should work:

SELECT CAST(DateColumn AS DATETIME) FROM OPENQUERY(DB2Server, 'select DB2DateColumn from DB2Schema.Table')

I've tried this earlier itself. When queried as a single column it shows the result as:

Code: