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.
Timezone Recap
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 Settings
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.
FetchXML Changes
If you filter based on a datetime value, you’ll also see the value changed in the generated FetchXML. You’ll see either a Z or [+/-]HH:mm timezone indicator at the end of the value to let Dataverse know how it should interpret the value.
Date Formats
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 or yyyy-MM-dd HH:mm:ss format, which is completely unambiguous.
I have a stupid question: i downloaded a fetch xml containing lastyear operator, converting it from fetxml to sql4cds it states: Unsupported SQL condition operator ‘lastyear’. How can I translate this
Thanks, I’ve found the source of this error now. Look out for another update shortly to resolve this.
Hi Mark.
I’m getting errors when working with dates and date-time in queries.
For example the statement: where createdon > ‘2020-07-01 00:00:00’
will result in:
The date-time format for 2020-07-01T00.00.00+00:00 is invalid, or value is outside the supported range.
See the Execution Plan tab for details of where this error occurred
If I change the settings to use the “TDS endpoint”, it works fine. But I have noticed that TDS is slower, so I prefer to have it off. Any tips for how to fix this issue will be much appreciated.
I love this tool!!!. Thanks
Interesting. What are your Windows regional/culture settings like – do you normally have “.” as the hour/minute separator?
At the moment the regional settings in that computer are set to “Danish”, which gives the date-time format: dd-MM-yyyy HH:mm:ss . I often switch to “English (United States)”, but I still get the same error.
Hello Mark,
I’ve just found about your SQL CDS plugin for XrmToolBox and it’s great!
Querying Dynamics through its Web API is awsome, especially when the TDS endpoint is not available.
I would like to ask you if there is a way to format dates in the output of a query executed through SQL CDS. I would like to convert the createdon datetime columns into a date (yyyy-MM-dd) or at least in a datetime without the milliseconds (yyyy-MM-dd HH:mm:ss), but the T-SQL convert function
seems to be ignored.
Any solution for this?
In the meanwhile, I found a working solution, by converting the datetimes to varchar.
CONVERT(VARCHAR(20),createdon,20) works fine to obtain a string formatted like yyyy-MM-dd
but
CONVERT(date, createdon) does not work as in SQL Server Management Studio .
It keeps displaying all the time part (despite truncating its value to 00:00:00.000)
Hi Joao, glad to hear you found a solution! You’re correct, internally SQL 4 CDS handles the
datetype identically to thedatetimetype, except that the time portion is stripped during the conversion. I’ll see if it can do anything better in a future version.The
CONVERTshould support all the standard T-SQL styles for datetime values, so you could use something likeCONVERT(varchar, createdon, 20)to get2022-03-10 11:44:32orCONVERT(varchar, createdon, 23)to get just2022-03-10i’m looking for datetrunc as i want to compare against januari first of current year or can it be achieved in another way.
DATETRUNC was only added to the latest preview of SQL Server and isn’t currently available in SQL 4 CDS. You can use DATEPART instead.
I’m experimenting with cursor and get an error:
DECLARE @week_num Nvarchar(100)
–SET @week_num = ‘2022 – 31’;
DECLARE Period_Cursor CURSOR FOR
SELECT psa_bookingperiod.psa_name
FROM psa_bookingperiod;
OPEN Period_Cursor;
result:
Unsupported statement: DECLARE Period_Cursor CURSOR FOR
SELECT psa_bookingperiod.psa_name
FROM psa_bookingperiod;
Correct, cursors are not currently supported in SQL 4 CDS.
oh, i thought i saw an example somewhere, my mistake thanks for your response