Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Dec 2011
    Posts
    29

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

    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.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Mr. Bean. Try 'select TIMESTAMP(db2-date-column) from DB2schema.Table'

  3. #3
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Stealth_DBA View Post
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Dec 2011
    Posts
    29

    Thumbs down

    Quote Originally Posted by Pat Phelan View Post
    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

  7. #7
    Join Date
    Dec 2011
    Posts
    29

    Red face

    Quote Originally Posted by Wim View Post
    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.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Dec 2011
    Posts
    29

    Post

    Quote Originally Posted by Wim View Post
    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.
    Last edited by Mr.Bean; 12-15-11 at 14:49. Reason: Typo

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Pat Phelan View Post
    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.

    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: ].

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have you tried it again after removing the ";" after sysibm.sysdummy1?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    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.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  14. #14
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by Wim View Post
    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

  15. #15
    Join Date
    Dec 2011
    Posts
    29
    Quote Originally Posted by aflorin27 View Post
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •