5

My computer/SQL Server 2014 uses Eastern Time Zone which is 5 hours behind UTC time. But when I run the following code, I get a 4-hour difference.

CREATE TABLE datetimeoffsetfunction(    
                                    servertime DATETIME2,
                                    utc_servertime DATETIME2,
                                    offset_datetime DATETIMEOFFSET,
                                    switch_offset DATETIMEOFFSET,
                                    to_datetime DATETIMEOFFSET
                                      );

INSERT INTO datetimeoffsetfunction
VALUES (  SYSDATETIME(), 
          SYSUTCDATETIME(), 
          SYSDATETIMEOFFSET(),
          SWITCHOFFSET(  SYSDATETIMEOFFSET(),  '+05:00'),
          TODATETIMEOFFSET( SYSDATETIME(), '+05:00')
          );

The result I got is 

servertime:         2016-07-09 23:59:16.4770556
utc_servertime:     2016-07-10 03:59:16.4770556
offset_datetime:    2016-07-09 23:59:16.4770556 -04:00
switch_offset:      2016-07-10 08:59:16.4770556 +05:00
to_datetime:        2016-07-09 23:59:16.4770556 +05:00

The servertime is my system time, and considering the daylight saving time, I can explain why there were only four hour difference between servertime and utc_servertime. But I did expect offset_datetime to be 2016-07-09 23:59:16.4770556 -05:00 unless SQL Serve now accounts for Daylight saving time.

So my question is Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014?

6

Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014?

No, SQL Server 2014 is not "DST-aware", nor is your test showing it to be.

First, your 4th and 5th tests -- SWITCHOFFSET( SYSDATETIMEOFFSET(), '+05:00') and TODATETIMEOFFSET( SYSDATETIME(), '+05:00') respectively -- are showing +05:00 because that is exactly what you told them to do.

Next, SYSDATETIMEOFFSET() is showing -04:00 because that offset is currently the true difference between your server's TimeZone (which does change, else it wouldn't show the correct time for approximately half of the year) and UTC, which doesn't change during the year. This, however, does indicate DST-awareness: it is the OS that is DST-aware, which is how the server's current time changes based on DST.

Being DST-aware means that the DST date boundaries for prior periods and for other TimeZones can be taken into account when switching TimeZones. And this requires knowledge of TimeZones. However, SQL Server (at least prior to SQL Server 2016) has no concept of TimeZones. Offsets are just that: a specific number of hours and minutes away from UTC. In contrast, a TimeZone is a rule that states a particular offset and if Daylight Saving is ever applied (some TimeZones do not participate in DST) and if DST is applied, what those date boundaries are (which are a matter of law and change over time, per each region).

Hence, being DST-aware means you have the ability to convert a date in the past to, or from, your current TimeZone, or any date between any two TimeZones, neither of which are the server's current TimeZone. Meaning, SQL Server being DST-aware would indicate that you could convert from:

  • UTC to Eastern Time (your server's current TimeZone), for any date in the year, for 20 years ago, and get the correct time, even though the DST date boundaries changed in the US during that time. But you can't since you can only calculate the current offset, which is -04:00 and have no way to determine when that offset would be -05:00.
  • Any non-Eastern TimeZone to any other non-Eastern TimeZone, for any date, and get the correct time. This is not possible because there is no chart of TimeZones that details which TimeZone is what offset for which dates, including historical shifts in those date boundaries, so that any particular date, of any particular year, will be adjusted correctly.

More info and some examples

As mentioned above, time zone offsets are not time zones: they are merely a property of a time zone. Meaning, the "US/Eastern" (a.k.a. "America/New_York") time zone (i.e. EST / EDT) is not identified as -04:00 any more than as -05:00; it is an area that has boundaries to the East and to the West, has a base offset of -05:00, and participates in Daylight Saving and so has an offset -- +01:00 -- and a specific start date and end date for when to apply that DST offset. You can check the info on TimeZoneDB.com.

The main database of this Time Zone info is maintained by the Internet Assigned Names Authority (IANA), and can be found at:

http://www.iana.org/time-zones

Time zone information is determined by individual governments. Some countries participate in DST, other do not. Some cities within various countries / states opt in or out of the parent regions rule. See the page for Daylight Saving Time Around the World 2016 on TimeAndDate.com for a chart the various start and end dates around the world (at least for 2016). And check out their page for Eastern Time (ET) for details including exceptions.

Also, whether or not a particular area observes DST, and if so, what the data boundaries are, changes over time. For example, in the United States, the DST start and end dates changed in late 2005, effective starting in 2007: US Daylight Saving Time Changed in 2005. Meaning, even if you know that the current rule is to apply the +01:00 offset to EST between March 13th and November 6th, then if you convert a DATETIME value of 2006-11-02 18:00:00 from UTC to ET, you would get 2006-11-02 14:00:00 which is wrong, because in 2006 DST ended on October 28th, making the correct Eastern Time be 2006-11-02 13:00:00.

Additional explanation in my DBA.StackExchange answer here: How to check the timezone for a given datetime?

How to handle DST (and converting between time zones) in SQL Server 2005 - 2014

The two main ways I know of to properly handle DST-awareness as well as convert between different time zones are:

  1. Matt Johnson's SQL Server Time Zone Support project:

    This is a pure T-SQL approach. Please see the ReadMe at the bottom of the main page linked above (i.e. the project name). Pay close attention to the ConvertZone and SwitchZone functions, as they convert between actual time zones, not mere offsets!

    Please also note that it states:

    Microsoft SQL Server 2008 R2 and higher are supported, including Azure SQL Database

  2. SQLCLR:

    Converting back and forth, between the server's local time zone and UTC is fairly simple using DateTime.ToLocalTime and DateTime.ToUniversalTime. This would work starting with SQL Server 2005.

    Stay away from the TimeZoneclass that has methods of the same names, due to the following warning shown on the TimeZone.ToLocalTime page:

    The ToLocalTime method recognizes only the current daylight saving time adjustment rule for the local time zone. As a result, it is guaranteed to accurately return the local time corresponding to a particular UTC time only during the period in which the latest adjustment rule is in effect. It may return inaccurate results if time is a historic date and time value that was subject to a previous adjustment rule.

    Converting between time zones is a bit trickier. There is a built-in .NET class that handles this, BUT it requires the calling Assembly to be marked as UNSAFE do to the TimeZoneInfo class having a Host Protection Attribute of MayLeakOnAbort (i.e. memory leak). People have used this class, and it might be fine when not calling its methods terribly frequently. But I did see at least one case of someone reporting what appeared to be a memory-leak when running this across many (hundreds of thousands or millions) of rows.

    Regarding the safe ToLocalTime and ToUniversalTime methods in the DateTime class: precompiled versions of these are available in the SQL# SQLCLR library that I created, but only in the Full version (not in the Free version). To show that these 2 methods do work, consider the situation mentioned above regarding the date boundaries change that occurred in the United States, starting in 2007:

    SELECT SQL#.Date_ToLocalTime('2007-10-31 20:23:45') AS [CurrentRule],
           SQL#.Date_ToLocalTime('2006-10-31 20:23:45') AS [PriorRule];
    

    Returns:

    CurrentRule                PriorRule
    2007-10-31 16:23:45.000    2006-10-31 15:23:45.000
    
  • Let me see if I understand this correctly. SQL Server was able to get the correct offset because it used my true , OS adjusted time and compared it to a static UTC time at the time of execution. This ensures that every time I run the code, the result would be correct. However, if I were to compare non real time, be it past or future time, to UTC, I may get incorrect result because SQL Server couldn't retrieve an adjusted time to use? And because awl serve doesn't know whether a particular time zone is in DST, it may also produce incorrect result for the same reason? – John Smith Jul 10 '16 at 14:21
  • @JohnSmithSr. Mostly yes. I have an example in another answer. I will post a link later when I get home, along with links for other resources to get DST-aware functions prior to SQL Server 2016. – Solomon Rutzky Jul 10 '16 at 14:47
  • @JohnSmithSr. I am done updating :-). – Solomon Rutzky Jul 11 '16 at 18:13
  • That is quite a lot of information. Appreciate it. Will check that out. – John Smith Jul 11 '16 at 21:43
1

Came in via google. Was looking at Solomon Rutzky post with the recommendation of Matt Johnson's SQL Server Time Zone Support project, excellent post first and foremost. According to his page DST is fully supported in MSSQL 2016

https://blogs.technet.microsoft.com/dataplatforminsider/2015/11/30/sql-server-2016-community-technology-preview-3-1-is-available/

https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql

It is as simple as:

select getdate() at time zone 'Mountain Standard Time'

Here is a link to all the different zones supported:

https://technet.microsoft.com/en-us/library/cc749073(v=ws.10).aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.