How to Cast datetime(7) Field to datetime(3)

[DataSync_Master]

I'm encountering an issue with a datetime column that includes extra milliseconds. I tried setting up a global rule transformation in Attunity to convert datetime(7) to datetime(3), but it didn't work. For example, converting "2020-10-23 17:12:44.0000000" to "2020-10-23 17:12:44.000" didn't apply as expected. I also tried configuring Qlik inline transformation in the table settings with datetime("YYYY-MM-DD HH:MM:SS.SSS"), but this resulted in null values across the field. Could you please advise on how to achieve the correct formatting?

Thank you!

[john_wang]

Hello @DataSync_Master ,

Welcome to Qlik Community forum and thanks for reaching out here!

Would you please share what's the source and target endpoints types? I'd like to confirm the behavior for you.

Regards,

John.

[john_wang · Accepted solution]

Hello @DataSync_Master ,

BTW, you may change the output data type to DATETIME(3) in transformation to see if it helps.

The screen sample:

Thanks,

John.

[DataSync_Master · Accepted solution]

Hi,
Thank you for your prompt response. My source is SQL Server and my target endpoint is GCS bucket.

[john_wang]

Thank you so much for your update @DataSync_Master , feel free to let us know if you need any additional assistance.

Best Regards,

John.