When I export TIMESTAMP data type from DB2 to MSSQL data types DATE, DATETIME or TIMESTAMP the fractional time is off by .001. If I export as data type VARCHAR() I get the correct data. Is there an explanation and/or solution for this?
I have exported through SSIS and PENTAHO.
DB2 2021-10-20-21.31.41.138000
MSSQL 2021-10-20 21:31:41.137
Thank you in Advance
>Solution :
The result you have is completely expected.
datetime is accurate to 1/300th of a second, so the value is expected. The closest you can get to 2021-10-20-21.31.41.138000 in a datetime is 2021-10-20T21:31:41.136666666666~ which is represented as 2021-10-20 21:31:41.137.
If you want the value to be accurate to 1/100000 of a second, use a datetime2(6), which could accurately store the value 2021-10-20T21:31:41.138000.