Does Transact-SQL have a funtion that can determine whether daylight savings was in effect on a given datetime?

Question

Does Transact-SQL have a funtion that can determine whether daylight savings was in effect on a given datetime?

Paul Kraemer 276

Hi,

I am using SQL Server Express (64-bit) version 14.0.1000.169. I have a database in which I have a table with a 'datetime' field. The PC on which this database resides is in the Eastern Time Zone (US and Canada, UTC-05:00). Our times here are adjusted for Daylight Savings Time - we move our time one hour forward in the spring and then one hour back in the fall.

I am querying this database from an application (built using a 3rd party Industrial Automation Process Visualization and Historian Software package) in which one of the features cannot handle date/time information in the same format that the SQL Server 'datetime' Data Type does. This particular feature requires that date/time information must be set with a value equaling the number of seconds since Jan 1, 1970. This application has a function called ITXCreateDateTime that allows me to determine the number of seconds since Jan 1, 1970 as follows:

SecondsSinceJan1_1970 = ITXCreateDateTime(year,month,day,hour,minute,second);

The value returned by this function reflects GMT time, so for me to use it, I have to add an Offset reflecting the difference between local time and GMT time. When daylight savings time is in effect, this offset is 18,000 (5 hours difference). When daylight savings time is not in effect, this offset is 14,400 (4 hours difference). What I am stuck on is that this application does not seem to have a function that allows me to determine whether Day Light Savings time was in effect on a past date. (It does have a function that allows me to determine whether Daylight Savings time is in effect on the current system date, but this does not help me for what I am trying to accomplish).

As I feel like I might not be able to accomplish what I want entirely within the application itself, I am wondering if Transact SQL has a feature that would allow me to determine whether Daylight Savings Time was in effect on a given date. If this is supported, and can include this as a Boolean in my query results, and use this in my application to determine the required offset.

Any suggestions will be greatly appreciated.

Thanks and best regards,
Paul

Accepted answer

5 additional answers

Your answer

Answer 1

Sorry, for solving the problem in the wrong direction. My lame excuse is that it is a lot more common that people ask about how to convert a Unix timestamp to the SQL Server data types and the other way round.

Here is a new function:

CREATE OR ALTER FUNCTION LocalTimeToUnixTime(@local datetime, @timezone varchar(50)) RETURNS bigint AS
BEGIN
   DECLARE @do datetimeoffset(3) = @local AT TIME ZONE @timezone
   SELECT @do = switchoffset(@do, '+00:00')
   RETURN datediff_big(second, '19700101', convert(datetime2(3), @do))
END
go
SELECT dbo.LocalTimeToUnixTime('2021-02-12 04:10:00.000', 'Central European Standard Time')

This time I made the time zone a parameter so I could test in my local time.

As for the data types, SQL Server has both datetime and datetime2 due to legacy. datetime has a range from 1754-01-01 to 9999-12-31, and a resolution of 3.33 ms. A datetime value takes up eight bytes. datetime2 has a range from 0001-01-01 (but always according to the Gregorian calendar) to 9999-12-31, and you can select from a resolution of full seconds (datetime2(0)) down to 100 ns (datetime2(7).) Stupidly, if you say datetime2 this is the same as datetime2(7), but you very rarely have need for a resolution of more than 1 ms, since this is what you get from the function sysdatetime(). (getdate() returns datetime, so here you only get 3.33 ms.) datetime(0) to datetime(2) take up six bytes, whereas datetime2(3) and datetime2(4) take up four bytes.

One advantage with datetime2 is that date strings on the format YYYY-MM-DD always work. This is not the case with datetime.

Answer 2

There is no built-in function for exactly this purpose. However, there is functionality to achieve what you are asking for. Try this function.

CREATE OR ALTER FUNCTION UnixTimeToEasternTime(@uxtime bigint) RETURNS datetime2(3) AS
BEGIN
   DECLARE @dawn datetimeoffset(3) = '19700101 00:00 +00:00'
   DECLARE @utctime datetimeoffset(3) = dateadd(second, @uxtime, @dawn)
   RETURN convert(datetime2(3), @utctime AT TIME ZONE 'Eastern Standard Time')
END

One caveat: this relies on what is in the Window registry, and I don't think it takes history in regard. So if you would have an old timestamp before the most recent DST change, you may get incorrect results.

I am using SQL Server Express (64-bit) version 14.0.1000.169.

This is the RTM version of SQL 2017, which you should absolutely not use. You should apply the most recent Cumulative Update, CU29 as soon as possible to get many bug fixes and improvements.

Answer 3

Thank you Erland,

I just applied the CU29.

I then tried the UnixTimeToEasternTime function you suggested. Please correct me if I am wrong, but this takes as a parameter the number of seconds since Jan 1, 1970 assuming GMT/UTC time. It then uses datetimeoffset in two steps to convert this to a date/time format that is understood by SQL Server (still indicative of GMT/UTC time). It then uses the convert function to convert this to the datetime2 type, expressed in the 'Eastern Time Format'.

If I could do this in reverse, it would work perfectly for me. In my table, I have 'datetime' data (I can easily change to 'datetime2' if it is preferable) that is indicative of the Eastern Time Zone. What I really need to do is determine the number of seconds since Jan 1, 1970 in the GMT/UTC time zone. If I can use the 'convert' function to convert the value in my 'datetime' field from Eastern to GMT/UTC, I'll be able to pass this GMT/UTC value to my application, I'd then be able to use the ITXCreateDateTime function in my application to calculate the number of seconds since Jan 1, 1970 correctly as I would not have to worry about daylight savings time (as GMT/UTC are not affected by Daylight Savings Time).

It seems like the 'datetimeoffset', 'convert', and 'AT TIME ZONE' tools you brought to my attention should be all I need to do this. I'll read up on these and see if I can figure out how to do this. If it seems like I am heading in the wrong direction, or if you can think of a better idea, your feedback will be greatly appreciated.

Thanks again,
Paul

Answer 4

Hi Erland,

Thank you for your help. I had a chance today to try your LocalTimeToUnixTime function and it seems as though this is going to work perfectly for me. I am already returning the values from this function into my 3rd party application. I just have to work through the details of using these values where I need them. I really appreciate your help.

I just have one question. You had mentioned that there is "One caveat: this relies on what is in the Window registry, and I don't think it takes history in regard. So if you would have an old timestamp before the most recent DST change, you may get incorrect results."

It seems now that with the limited amount of data I have stored (going back to 2021) and my limited testing, the function will work perfectly for me. After I complete this project, the only new data that will be stored will be from whatever the current date is at that time and moving forward from there. My requirement in this project is that I configure the PC I now have on my desk to serve as the user interface for a machine my company built. Once I deploy it, it will be locked down to whatever extent is possible - i.e. not connected to the Internet, no updates, and no-one tinkering with Windows settings unless absolutely necessary. If a change to the system does become necessary, it will be done by me (or someone with an equal or better understanding of how it works). With this being the case, do you think I should be concerned that a registry issue sometime down the road might cause the function to behave differently?

Thanks again for the help. I really do appreciate it!

Best regards,
Paul

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-05-30T21:16:34.587+00:00

So what could happen is that the DST rule changes. In fact, a poster claimer here a month back or so that the US Senate had unanimously voted for having DST all year through, and thus there would be no changes of time zones through the year. I don't know the full story here, but I guess you know more than I do.

Anyway, if there are such changes, Microsoft will ship those through Windows Update, and you will want to have that change.
Paul Kraemer 276 Reputation points

2022-05-31T14:30:45.737+00:00

Thanks Erland,

That makes a lot of sense. At least if I pay attention to any planned DST rule changes, I'd know when they are coming and be able to prepare in advance. If we do give up on adjusting our time twice per year, it would only require a very simple adaptation to the code in my application.

Thank you again for your help!

Best regards,
Paul

Answer 5

Hi @Paul Kraemer
How about building a DST table that has the start and end Daylight Savings Time per year and check the datetime against that.
Check this sample:

Create Table #TZCalendar  
(  
  [Year]        DATE PRIMARY KEY,  
  UTC_DST_Start SMALLDATETIME NOT NULL,  
  UTC_DST_End   SMALLDATETIME NOT NULL,  
  ET_DST_Start  AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -4, UTC_DST_Start)),  
  ET_DST_End    AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -5, UTC_DST_End))  
);  
Go  
SET DATEFIRST 7;  

;WITH cte(d,p) AS   
(  
  -- all the years from 2000 through 50 years after the current year:  
  SELECT TOP (YEAR(GETDATE())-2000+51) DATEADD(YEAR,number,'20000101'),  
    CASE WHEN number < 7 THEN 1 ELSE 0 END -- year < 2007 = 1, else 0  
    FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number  
)  
INSERT #TZCalendar([Year],UTC_DST_Start,UTC_DST_End)  
SELECT d,  
 -- First Sunday in April (< 2007) or second Sunday in March (>= 2007):  
 DATEADD(HOUR, 7, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,2+p,d))+1)%7  
    +(7*ABS(p-1)),DATEADD(MONTH,2+p,d))),  
 -- Last Sunday in October (< 2007) or first Sunday in November (>= 2007):  
 DATEADD(HOUR, 6, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,10,d))+1)%7  
    -(7*p),DATEADD(MONTH,10,d)))  
FROM cte  
ORDER BY d;  
  
DECLARE @DateToCheck DATETIME   
SET @DateToCheck='2022-05-30 17:20:00.000'  
SELECT CASE WHEN EXISTS (SELECT * FROM #TZCalendar WHERE @DateToCheck BETWEEN ET_DST_Start AND ET_DST_End )  
            THEN 'Daylight Savings Time'  
            ELSE 'Standard Time' END AS IF_DST

Refer to this article for more details: Handle conversion between time zones in SQL Server

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Does Transact-SQL have a funtion that can determine whether daylight savings was in effect on a given datetime?

5 additional answers

Your answer