none
Convert DB2 timestamp to SQL Server datetime.

    Question

  •  I read the following in this in this forum. 

     

    Problem:

    Convert DB2 timestamp to SQL Server datetime.  

    Solution:

    to_char(columnname, 'YYYY-MM-DD HH24:MI:SS') from DB2 in Source query.

    make sure it is type STR in advanced Editor.

    use derived column, to DATE[] with fast parse property as noted above.

    Edit: I didn't even need a derived column above, the to_char is an implicit conversion to the Datetime in SQL Server. 

     

    In advanced Editor, where it should be changed to STR?.  There are two options. External Columns and Output Columns.  Which one should be changed?.  Should it changed to STR[DT_STR]?.


    NSG12
    Friday, January 23, 2009 10:28 PM

Answers

  • The external column type is decided by actual datatype in DB2 which will be refreshed automatically each time the edit dialog is opened, so it can't be changed. The output column is that in pipeline buffer, so its datatype can be changd.

    In the advanced dialog, select given column in Output Columns subtree and change the data type to string [DT_STR] in right property window.

    Saturday, January 24, 2009 2:10 AM

All replies

  • The external column type is decided by actual datatype in DB2 which will be refreshed automatically each time the edit dialog is opened, so it can't be changed. The output column is that in pipeline buffer, so its datatype can be changd.

    In the advanced dialog, select given column in Output Columns subtree and change the data type to string [DT_STR] in right property window.

    Saturday, January 24, 2009 2:10 AM
  • If you just bring it in as a date time you should be fine.  You will need to ensure that it is not a date prior to 1/1/1753, the lowest date time in SQL Server.  In DB2 this dates are valid all the way down to 1/1/0001.  You will need to note that DB2 timestamp has more precision than that of SQL 2005 and you can run in to some possible loss of data.

    As to the problem stated above, I would, and I believe that this was the original intent,  use the to_char as part of the query to return the data from DB2.  That way the data enters the pipe as a character string.


    Please mark answered posts. Thanks for your time.
    Monday, January 26, 2009 1:44 PM
    Moderator