Not able to store milliseconds on the dedicated sql and on-premises db

Question

Team,

We are fetching the data from azure cosmos db container and loads it into dedicated sql and on-premises using azure synapse pipelines.
We created one dynamic pipeline (one for dedicated and one for on-premises) to load all the tables.

However, cosmos db date fields contain milliseconds which are not flowing into the target systems. It all gets converted to zeros.

For example, 01-02-2023 15:25:39.1234567 is loaded as 01-02-2023 15:25:39.0000000. Target data type is datetime2(7).

When we previewed the source data in the pipeline, it shows with actual values (like 1234567 ms).

what do you think the issue here?

Thanks,
Jakeer. Shaik

Answer 1

Hello @Jakeer Shaik ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is to know as to why the miliseconds are not showing up on the sink side , please do let us know if its not accurate.

I did tried to repro the same on dedicated sql and i am surprised to find that it does just fine .

after running the pipeline this is output .

If I were you , i could have tried to insert a record in the sink table with the date ( using TSQL ) and then query on the table to check the record . I did tried with with datetime also but I was not able to repro .

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hi @HimanshuSinha-msft

Is there any update on this?

Thanks,
Jakeer. Shaik