Time zone differences in transfer of data between Dynamics 365 (via Azure Data Factory) to Azure SQL Server

0
votes

I am working with a client to transfer date records over from Dynamics 365 via Data Factory over to an Azure SQL Server for further transfer to an external system.

The client has set up their Dynamics 365 instance in a way where the date fields follow the "UserLocal" behaviour, as opposed to "DateOnly", which would have been the suggested option as per the documentation:

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/developer/behavior-format-date-time-attribute.

They are not able to change this behaviour due to business logic that has been built on top of it.

When we transfer these date rows over to Azure SQL Server we get the date values adjusted by the system timezone (UTC), which for us is a difference of -1 hrs (our time zone is UTC +1). This results in a scenario where we get incorrect dates in the SQL tables.

Is there a way to easily adjust this in Azure Data Factory for a copy pipeline with the Common Data Service connector as source and SQL Server as sink? The date values are translated over as string values into the SQL Server.

Thank you in advance and I look forward to your thoughts on the above!

1
azureazure-sql-databasedynamics-crmazure-data-factorymicrosoft-dynamics
SQL Azure Managed Instance can set timezones. Separately, you could use the datetimeoffset type in SQL Server/SQL Azure to try to encode the timezone in the type. – Conor Cunningham MSFT
Thanks for the quick response! I will give it a go and report back. – thereceptionist
Thanks for the tip, I was able to create a view with the local timezone. Is there a way to display the datetimeoffset value in local time? So instead of 2021-01-13 23:00:00.000 +01:00” get "2021-01-14 00:00:00.000"? Thanks again for your kind help and cheers! – thereceptionist
I am unaware of a way to make it display local time in SQL DB from datetimeoffset. If you know that the database is only used from one timezone, you could hardcode that I presume in the view using dateadd or similar. – Conor Cunningham MSFT
Hi @thereceptionist, Thanks for Connor's help and for your another question, I suggest you post a new question and more people can help you. I just summarize the comments and help Conor post it as answer, this can be beneficial to other community members. Thank you. – Leon Yue

1 Answers

1
votes

Thanks for @Conor Cunningham's help:

"SQL Azure Managed Instance can set timezones. Separately, you could use the datetimeoffset type in SQL Server/SQL Azure to try to encode the timezone in the type."

He provides a very useful way for us. And for your another question "Is there a way to display the datetimeoffset value in local time?", @ Conor Cunningham MSFT also gives the suggestion: "If you know that the database is only used from one timezone, you could hardcode that I presume in the view using dateadd or similar".

I think you could post a new question and more people can help you. I help him post it as answer and this can be beneficial to other community members. Thank you.