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.

13 thoughts on “Date/Time handling in SQL 4 CDS”

  1. 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

  2. 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

      1. 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.

  3. 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?

    1. 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


      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)

      1. Hi Joao, glad to hear you found a solution! You’re correct, internally SQL 4 CDS handles the date type identically to the datetime type, except that the time portion is stripped during the conversion. I’ll see if it can do anything better in a future version.

        The CONVERT should support all the standard T-SQL styles for datetime values, so you could use something like CONVERT(varchar, createdon, 20) to get 2022-03-10 11:44:32 or CONVERT(varchar, createdon, 23) to get just 2022-03-10

  4. i’m looking for datetrunc as i want to compare against januari first of current year or can it be achieved in another way.

  5. 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;
    Unsupported statement: DECLARE Period_Cursor CURSOR FOR
    SELECT psa_bookingperiod.psa_name
    FROM psa_bookingperiod;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.