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

Mr.Bean · #1 (**permalink**) 12-14-11, 14:02

Hi All,

I need to convert a column in DB2 Linked Server of Datatype 'DATE' to a column in SQL Server 2000 of Datatype 'DATATIME'.

I have tried both the Function and CastConvert Functions and neither the Function nor the Cast & Convert helped me out.

The Queries I've used are:

SELECT * FROM OPENQUERY(DB2Server, 'select {fn convert(DB2DateColumn , SQL_DATE)} from DB2Schema.Table')

SELECT * FROM OPENQUERY(DB2Server, 'select Cast(DB2DateColumn As DateTime ) from DB2Schema.Table')

SELECT * FROM OPENQUERY(DB2Server, 'select Convert(DateTime, DB2DateColumn)from DB2Schema.Table')

Can somebody help me out.

Thanks.

Stealth_DBA · #2 (**permalink**) 12-14-11, 15:20

Mr. Bean. Try 'select TIMESTAMP(db2-date-column) from DB2schema.Table'

Mr.Bean · #3 (**permalink**) 12-14-11, 15:48

Quote:

Originally Posted by Stealth_DBA

Mr. Bean. Try 'select TIMESTAMP(db2-date-column) from DB2schema.Table'

Hi Stealth_DBA

It has thrown me the following error when I executed the above query

:

[OLE/DB provider returned message: [Microsoft][ODBC DB2 Driver]A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42605, SQLCODE: -170]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.

Pat Phelan · #4 (**permalink**) 12-14-11, 18:32

A lot depends on which version of SQL Server you are using and which DB2 driver you are using. I'm using SQL 2008 R2 Developer Edition and the Microsoft DB2 OLEDB driver on Windows, and DB2 Version 8 on ZOS. I can run the following code nicely:

Code:

SELECT DATEADD(YEAR, 1, PSMDATE), *
   FROM OpenQuery(MyLinkedServerName, N'
      SELECT *
         FROM SYSIBM.SYSPSM
   ')

-PatP

Wim · #5 (**permalink**) 12-14-11, 18:52

Have you tested that you can get anything out of DB2 without an error? Like
SELECT 1 as aValue FROM sysibm.sysdummy1

If your connection did work, you may try to get the date from DB2 in string format and convert it back to a DATETIME in SQL Server. Clumsy, but when everything else fails, this solution will most likely work.

When you do a CAST of the DATE to a VARCHAR, DB2 will convert it into a format specific for the territory code of your database. Suppose your territory code = US, the format will be MM/DD/YYYY. To go from there to a DATETIME, you need CONVERT(DATETIME, DateString, 101) in SQL Server.

Code:

select CAST(DB2DateColumn AS VARCHAR(10)) as DateString from DB2schema.Table

When you come from a SQL Server background, DB2 may seem different at start.
DB2 is not as forgiving as SQL Server about diverting from the SQL standards, things like
SELECT 1
or
SELECT 1 aValue FROM sysibm.sysdummy1
won't work.
You will need to adhere to the SQL standard syntax, like in
SELECT 1 as aValue FROM sysibm.sysdummy1

Mr.Bean · #6 (**permalink**) 12-15-11, 11:42

Quote:

Originally Posted by Pat Phelan

A lot depends on which version of SQL Server you are using and which DB2 driver you are using. I'm using SQL 2008 R2 Developer Edition and the Microsoft DB2 OLEDB driver on Windows, and DB2 Version 8 on ZOS. I can run the following code nicely:

Code:

SELECT DATEADD(YEAR, 1, PSMDATE), *
   FROM OpenQuery(MyLinkedServerName, N'
      SELECT *
         FROM SYSIBM.SYSPSM
   ')

-PatP

I am using SQL Server 2000 on Microsoft Windows Server 2003 R2 with OLE DB Driver to connect to DB2. I am not sure about the DB2 Version I am using as I just has read access of the data to it.

I've tried running the above query and it gave me an empty result.

Looking forward for more help.

Thanks
Mr.Bean

Mr.Bean · #7 (**permalink**) 12-15-11, 12:23

Quote:

Originally Posted by Wim

Have you tested that you can get anything out of DB2 without an error? Like
SELECT 1 as aValue FROM sysibm.sysdummy1

If your connection did work, you may try to get the date from DB2 in string format and convert it back to a DATETIME in SQL Server. Clumsy, but when everything else fails, this solution will most likely work.

When you do a CAST of the DATE to a VARCHAR, DB2 will convert it into a format specific for the territory code of your database. Suppose your territory code = US, the format will be MM/DD/YYYY. To go from there to a DATETIME, you need CONVERT(DATETIME, DateString, 101) in SQL Server.

Code:

select CAST(DB2DateColumn AS VARCHAR(10)) as DateString from DB2schema.Table

When you come from a SQL Server background, DB2 may seem different at start.
DB2 is not as forgiving as SQL Server about diverting from the SQL standards, things like
SELECT 1
or
SELECT 1 aValue FROM sysibm.sysdummy1
won't work.
You will need to adhere to the SQL standard syntax, like in
SELECT 1 as aValue FROM sysibm.sysdummy1

I come from a SQL Server Background and I have no idea about DB2. I just access DB2 Tables through a Linked Server created from SQL Server.

The above queries didn't give me any output instead gave me an error.

Wim · #8 (**permalink**) 12-15-11, 12:52

First get your connection up and running before you go any further.

Run my simple query of the one from Pat Phelan to test and retest your connection until it works. Only then start adding "real" queries.

Mr.Bean · #9 (**permalink**) 12-15-11, 13:46

Quote:

Originally Posted by Wim

First get your connection up and running before you go any further.

Run my simple query of the one from Pat Phelan to test and retest your connection until it works. Only then start adding "real" queries.

Well I think the connection is UP and running because, I am able load the data from DB2 to SQL Server using DTS Package however, failing to load using T-SQL.

Pat Phelan · #10 (**permalink**) 12-15-11, 16:46

Please try using:

Code:

SELECT d AS Test1
   FROM OpenQuery(MyLinkedServer, 'SELECT CURRENT_DATE AS d
      FROM sysibm.sysdummy1;
   '  )
GO
SELECT d, DATEADD(year, 1, d) AS Test2
   FROM OpenQuery(MyLinkedServer, 'SELECT CURRENT_DATE AS d
      FROM sysibm.sysdummy1;
   '  )

Please copy-and-paste the results.

-PatP

Mr.Bean · #11 (**permalink**) 12-16-11, 17:42

Quote:

Originally Posted by Pat Phelan

Please try using:

Code:

SELECT d AS Test1
   FROM OpenQuery(MyLinkedServer, 'SELECT CURRENT_DATE AS d
      FROM sysibm.sysdummy1;
   '  )
GO
SELECT d, DATEADD(year, 1, d) AS Test2
   FROM OpenQuery(MyLinkedServer, 'SELECT CURRENT_DATE AS d
      FROM sysibm.sysdummy1;
   '  )

Please copy-and-paste the results.

-PatP

I am sorry it has thrown me an error.

Quote:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC DB2 Driver]An unexpected token ; was found. Expected tokens may include: ( . FULL RIGHT LEFT INNER JOIN QUERYNO HAVING WHERE GROUP ORDER. SQLSTATE: 42601, SQLCODE: -104]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80004005: ].
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC DB2 Driver]An unexpected token ; was found. Expected tokens may include: ( . FULL RIGHT LEFT INNER JOIN QUERYNO HAVING WHERE GROUP ORDER. SQLSTATE: 42601, SQLCODE: -104]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80004005: ].

Wim · #12 (**permalink**) 12-16-11, 17:54

Have you tried it again after removing the ";" after sysibm.sysdummy1?

aflorin27 · #13 (**permalink**) 12-19-11, 03:20

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.

Mr.Bean · #14 (**permalink**) 12-27-11, 12:01

Quote:

Originally Posted by Wim

Have you tried it again after removing the ";" after sysibm.sysdummy1?

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

Invalid Object Name

Mr.Bean · #15 (**permalink**) 12-27-11, 12:02

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.

I've tried all of them but none helped me. Anyways thanks for the concern

Internet Services

Web Development

Digital Marketing

Consumer Tech