The ways different datetime columns in Dataverse/D365 behave across timezones is already the subject of plenty of blog articles. In this one I’d like to clarify how they work in SQL 4 CDS. Please pay particular attention to this if you’re updating any datetime values!
⚠ Warning: For SQL 4 CDS to operate as expected, the timezone setting in your local Windows configuration must match the timezone in your Dataverse/D365 user configuration.
You can configure datetime columns in Dataverse to either use the user’s timezone (“User Local”), or be timezone independent.
A user local column (the default) will store the value in UTC behind the scenes. When a user enters a new value, Dataverse translates it from their configured timezone to UTC before storing it. When the user looks at the value on a form, Dataverse translates it back to their timezone. The value will appear differently depending on the timezone of the user that’s looking at the record.
Timezone independent fields on the other hand are always just stored and shown exactly as they are entered. The same value will be shown regardless of the timezone of the user.
Confusingly, you can also configure date-only columns as user-local. In this case Dataverse treats the date you enter as being a datetime value at midnight on the selected date. That datetime value is then translated back and forth between the user’s timezone and UTC in the same way as a regular datetime column. I can’t see the value in this and would recommend ensuring that a date-only column uses the “Date Only” behaviour when you create it.
SQL 4 CDS lets you query or update datetime values using either UTC (the default) or your local timezone. You can select which to use in the Settings dialog:
Whichever option you select will affect all processing of datetime columns configured to use the user-local behaviour, such as:
- display – SQL 4 CDS will convert the values to the selected timezone before showing them in the grid view
- filtering – if you apply a filter like
createdon > '2021-05-21 13:00', the value will be interpreted as either a UTC or local timezone value as appropriate
- updating – when you update a value like
UPDATE account SET new_datetime = '2021-05-21 13:00', that value will be interpreted as being in the selected timezone
If you try to update records to a new datetime value and you’ve got UTC times selected, when you view the record in your browser you’ll see a different value displayed unless you happen to be in the UTC timezone yourself.
If you filter based on a datetime value, you’ll also see the value changed in the generated FetchXML. You’ll see either a
[+/-]HH:mm timezone indicator at the end of the value to let Dataverse know how it should interpret the value.
T-SQL defines how literal datetime values like ‘2021-05-21’ are interpreted, and SQL 4 CDS should handle them in the same way. However, for clarity and consistency I’d highly recommend using the ISO standard
yyyy-MM-dd HH:mm:ss format, which is completely unambiguous.