DateTime Milliseconds issue with AZURE bulk load

[Sandy]

Hi,

When migrating data from DB2 to Azure using Azure bulk load snap, the milliseconds are not populating in Azure SQL tables ( data type used is datetime2(7) ). For milliseconds the data is coming as ‘0000000’.
On DB2 side the, timestamp format is yyyy-MM-dd HH:mm:ss.SSSSSS.

I am using substring to extract the date time and I see date with milliseconds is coming thru the mapper but the same is not inserting into Azure tables. Itried hardcoding the milliseconds in the mapper and still it didnt work. Please let me know if there is any work around to populate the milliseconds.

[bojanvelevski]

Hi @Sandy,

Can you send one sample date that goes into Azure?

Thank you

[Sandy]

Hi

Here is the sample date which goes from DB2 to Azure SQL.
‘2022-11-16 03:27:20.038261’.

I am using this to extract the timestamp

($LAST_UPDT_TIMESTAMP != null) ? ( $LAST_UPDT_TIMESTAMP .toString().substr(0,10) +" "+ $LAST_UPDT_TIMESTAMP .toString().substr(11,8) + “.” + $LAST_UPDT_TIMESTAMP .toString().substr(14,7) ) : null

Thank you.

[bojanvelevski]

I cannot spot anything that might be the cause of the issue, except that the sample format you sent earlier is with 6 digits of milliseconds, and you need to send the value with 7 digits.

The expression can also be simplified by using

.toLocaleDateTimeString({"format":"yyyy-MM-dd HH:mm:ss.SSSSSSS"}) instead of manually combining the format where we can easily miscalculate indexes and send out a wrong date.

Regards,
Bojan