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.
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
- 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:
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
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:
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
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
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];
2007-10-31 16:23:45.000 2006-10-31 15:23:45.000