topic Re: How to Cast datetime(7) Field to datetime(3) in Qlik Replicate

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

DataSync_Master — Mon, 28 Oct 2024 23:57:50 GMT

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!

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

john_wang — Tue, 29 Oct 2024 01:13:39 GMT

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.

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

john_wang — Tue, 29 Oct 2024 02:03:32 GMT

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.

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

DataSync_Master — Tue, 29 Oct 2024 02:24:12 GMT

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

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

john_wang — Tue, 29 Oct 2024 02:28:13 GMT

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

Best Regards,

John.