locked
Migrate data error with DB2 Timestamp column RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am using SSMA 6.0 for DB2. When trying to migrate data with a table have timestamp column, it fails with an error "Hour, Minute, and Second parameters describe an unrepresentable DateTime." however i don't see any issues with the source data. Does anyone knows a solutiion or workaround with this error? any help will be greatly appreciated.

    Thanks!

    Li Fan

    Monday, July 13, 2015 1:31 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Li Fan,

    Based on your description, the source data of DB2 timestamp column is like yyyy-mm-dd hh:mm:ss.nnnnnn but SQL Server datetime column format is yyyy-mm-dd hh:mm:ss.nnn.

    To resolve this issue, you can change the source data in DB2 to string and truncate nnnnnn to nnn and then migrate the data to SQL Server datetime column. Another option is to map the DB2 timestamp column to SQL Server datetime2 column which can store the data that in yyyy-mm-dd hh:mm:ss.nnnnnn format.


    Thanks,
    Lydia Zhang

    Lydia Zhang
    TechNet Community Support


    Thursday, July 16, 2015 2:57 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    The SQL Server data type "DateTime" can presents Dates between the years 1753 and 9999; do you may be have Dates out of this range in your DB2 database?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, July 13, 2015 1:35 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Olaf,

    Even after i have update all rows in the TIMESTAMP column with DB2 Current TimeStamp value (like 2015-07-13 11:54:45.225000), re-try the migrate data for my table, same error occured and same numbers of records partially loaded.

    wired error?? Any suggestion?

    Thanks

    Li Fan

    Monday, July 13, 2015 7:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Li Fan,

    Based on your description, the source data of DB2 timestamp column is like yyyy-mm-dd hh:mm:ss.nnnnnn but SQL Server datetime column format is yyyy-mm-dd hh:mm:ss.nnn.

    To resolve this issue, you can change the source data in DB2 to string and truncate nnnnnn to nnn and then migrate the data to SQL Server datetime column. Another option is to map the DB2 timestamp column to SQL Server datetime2 column which can store the data that in yyyy-mm-dd hh:mm:ss.nnnnnn format.


    Thanks,
    Lydia Zhang

    Lydia Zhang
    TechNet Community Support


    Thursday, July 16, 2015 2:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Lydia,

    I am using SSMA 6.0 for DB2. which map the DB2 timestamp column to SQL Server datetime2 column. but this tool is not consistent when convert this DB2 timestamp column.  i have tables converted with no errors. but i also have some tables consistently fail at fixed number of rows or fixed %. the error i list above is just for one particular table.

    Do you familiar with SSMA 6.0 for DB2? Do you know any other efficient ways to migrate DB timestamp column to SQL Server datetime2 column.

    Thanks,

    Li Fan

    Wednesday, July 22, 2015 5:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Li Fan,

    Since SSMA 6.0 for DB2 cannot properly handle the DB2 timestamp column, I recommend you configure linked server between SQL Server and DB2, then pull DB2 data from timestamp column into SQL Server with the OPENQUERY method.

    There is a similar blog for your reference.
    https://www.simple-talk.com/blogs/2006/08/15/date-errors-pulling-db2-data-into-sql-server/


    Thanks,
    Lydia Zhang

    Lydia Zhang
    TechNet Community Support



    Thursday, July 23, 2015 2:13 AM