Why is TIMESTAMP fractional seconds off by .001 when exported from DB2 to MSSQL data type DATE, DATETIME, TIMESTAMP not as VARCHAR()

When I export TIMESTAMP data type from DB2 to SQL Server 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

SQL Server 2021-10-20 21:31:41.137

Thank you in advance

Solution 1:

The result you have is completely expected.

datetime is accurate to 1/300th of a second. 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 on screen as 2021-10-20 21:31:41.137 (as the displayed value is accurate to 3 digits, and rounded).

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.