Determining daylight-saving time in Central European Time

Question

(We are on SQL Server 2008).

The following constellation:

Our applications passes date&time around (in the most horrible way possible).

We want to simplify this. Instead of a culture-specific string like '31.12.2019', we're now passing an ecma-timestamp (the number of milliseconds between the point in time in UTC and 1970-01-01 00:00:00 UTC that is).

Now an additional complication:

Our pitiful application historically has saved all datetime values in the database as local time WITH DAYLIGHT-SAVING (central European summer or winter time, depending on the date) instead of UTC.

Now, central European summer time (CEST) is UTC+2, while central European winter time (CET) is UTC+1.

For the adjustment between summer and winter time, the following rules are applied:

• The change from winter time to summer time is on the last Sunday of March

  • On the last Sunday morning of March, the clocks will be put forward from 02:00 to 03:00. (one 'loses' an hour)

• The change from summer time to winter time is on the last Sunday of October:

  • On the last Sunday morning of October, the clocks will be put backward from 03:00 to 02:00 (one wins an hour)

As you might realize from looking at the definition, the change from summer to winter time presents a discontinuity range, in which a given local-time value can be both summer or winter time... (but not the change from winter to summer time)

Now, I have written the below functions to convert local/UTC-time into an ECMA-timestamp, and you can specify if the input datetime is UTC or localtime.

I haven't had the time to test it all too extensively, but I'd like to collect a second opinion on how to handle the times between 02 and 03 o'clock at the last Sunday of October...

• Would you handle the conversion the same? (apart from the fact that ideally, the conversion would be avoided / data changed to UTC)
• Do you spot any errors?
• Thoughts on what best to do between 02 and 03

PRINT 'Begin Executing "01_fn_dtLastSundayInMonth.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_dtLastSundayInMonth]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fn_dtLastSundayInMonth]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 



/*
-- This is for testing
SET DATEFIRST 3; -- Monday

WITH CTE AS (

    SELECT 1 AS i, CAST('20190101' AS datetime) AS mydate  
    UNION ALL 

    SELECT i+1 AS i, DATEADD(month, 1, CTE.mydate) AS mydate 
    FROM CTE WHERE i < 100 
)

SELECT -666 AS i, dbo.fn_dtLastSundayInMonth('17530101') AS lastSundayInMonth, dbo.fn_dtLastSundayInMonth('17530101') AS Control 

UNION ALL 

SELECT -666 AS i, dbo.fn_dtLastSundayInMonth('99991231') AS lastSundayInMonth, dbo.fn_dtLastSundayInMonth('99991231') AS Control 

UNION ALL 

SELECT 
     mydate 
    ,dbo.fn_dtLastSundayInMonth(mydate) AS lastSundayInMonth 
    ,dbo.fn_dtLastSundayInMonth(mydate) AS lastSundayInMonth 
    ,DATEADD(day,DATEDIFF(day,'19000107', DATEADD(MONTH, DATEDIFF(MONTH, 0, mydate, 30))/7*7,'19000107') AS Control 
FROM CTE 

*/


-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Return Datum von letztem Sonntag im Monat 
--                mit gleichem Jahr und Monat wie @in_DateTime
-- =====================================================================
ALTER FUNCTION [dbo].[fn_dtLastSundayInMonth](@in_DateTime datetime )
RETURNS DateTime
AS
BEGIN
    -- Abrunden des Eingabedatums auf 00:00:00 Uhr
    DECLARE @dtReturnValue AS DateTime  
    -- 26.12.9999   SO
    IF @in_DateTime >= CAST('99991201' AS datetime) 
        RETURN CAST('99991226' AS datetime); 

    -- @dtReturnValue is now last day of month 
    SET @dtReturnValue = DATEADD 
        (
             DAY 
            ,-1
            ,DATEADD
            (
                 MONTH
                ,1
                ,CAST(CAST(YEAR(@in_DateTime) AS varchar(4)) + RIGHT('00' + CAST(MONTH(@in_DateTime) AS varchar(2)), 2) + '01' AS datetime) 
            )
        )
    ;

    -- SET DATEFIRST 1 -- Monday - Super easy ! 
    -- SET DATEFIRST != 1 - PHUK THIS ! 
    SET @dtReturnValue = DATEADD
                        (
                            day
                            ,
                             -
                             (

                                (
                                    -- DATEPART(WEEKDAY, @lastDayofMonth) -- with SET DATEFIRST 1 
                                    DATEPART(WEEKDAY, @dtReturnValue) + @@DATEFIRST - 2 % 7 + 1 
                                )
                                %7
                            )
                            , @dtReturnValue
    );

    RETURN @dtReturnValue; 
END


GO



GO 


PRINT 'Done Executing "01_fn_dtLastSundayInMonth.sql"' 


GO 





PRINT 'Begin Executing "02_fn_dtIsCEST.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_dtIsCEST]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fn_dtIsCEST]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 




-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fn_dtIsCEST('2019-03-31T01:00:00'), dbo.fn_dtIsCEST('2019-03-31T04:00:00') 
ALTER FUNCTION [dbo].[fn_dtIsCEST](@in_DateTime datetime )
RETURNS bit 
AS
BEGIN
    DECLARE @dtReturnValue AS bit   

    -- https://www.linker.ch/eigenlink/sommerzeit_winterzeit.htm

    -- the change from winter time to summer time is on the last sunday of March
    -- the clocks will be put forward from 02:00 to 03:00. (one 'loses' an hour)

    -- the change from summer time to winter time is on the last sunday of October:
    -- the clocks will be put backward from 03:00 to 02:00 (one wins an hour).

    DECLARE @beginSummerTime datetime 
    SET @beginSummerTime = dbo.fn_dtLastSundayInMonth(DATEADD(MONTH, 2, DATEADD(YEAR, YEAR(@in_DateTime)-1900, 0)) )  
    SET @beginSummerTime = DATEADD(HOUR, 2, @beginSummerTime) 

    DECLARE @beginWinterTime datetime 
    SET @beginWinterTime = dbo.fn_dtLastSundayInMonth(DATEADD(MONTH, 9, DATEADD(YEAR, YEAR(@in_DateTime)-1900, 0)) )   
    SET @beginWinterTime = DATEADD(HOUR, 2, @beginWinterTime) 

    SET @dtReturnValue = 0; 
    IF @in_DateTime >= @beginSummerTime AND @in_DateTime < @beginWinterTime 
    BEGIN 
        SET @dtReturnValue = 1;
    END 

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "02_fn_dtIsCEST.sql"' 


GO 





PRINT 'Begin Executing "03_fn_dtToEcmaTimeStamp.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_dtToEcmaTimeStamp]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fn_dtToEcmaTimeStamp]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 




-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fn_dtToEcmaTimeStamp('2019-03-31T01:00:00', 1), dbo.fn_dtToEcmaTimeStamp('2019-03-31T04:00:00', 1) 
ALTER FUNCTION [dbo].[fn_dtToEcmaTimeStamp](@in_DateTime datetime, @in_convert_to_utc bit)
RETURNS bigint 
AS
BEGIN
    DECLARE @dtReturnValue AS bigint    

    IF @in_convert_to_utc = 1 
    BEGIN
        SET @in_DateTime = 
        CASE WHEN dbo.fn_dtIsCEST(@in_DateTime) = 1 
            THEN DATEADD(HOUR, -2, @in_DateTime) 
            ELSE DATEADD(HOUR, -1, @in_DateTime) 
        END;
    END 

    SET @dtReturnValue = 
        CAST
        (
            DATEDIFF
            (
                HOUR
                ,CAST('19700101' AS datetime)
                ,@in_DateTime 
            )
            AS bigint
        ) *60*60*1000
        + 
        DATEDIFF
        (
             MILLISECOND 
            ,CAST(FLOOR(CAST(@in_DateTime AS float)) AS datetime) 
            ,@in_DateTime 
        ) % (60*60*1000)
    ;

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "03_fn_dtToEcmaTimeStamp.sql"' 


GO 





PRINT 'Begin Executing "04_fn_dtFromEcmaTimeStamp.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_dtFromEcmaTimeStamp]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fn_dtFromEcmaTimeStamp]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 



-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fn_dtFromEcmaTimeStamp('1551437088122', 1), dbo.fn_dtFromEcmaTimeStamp('1554069600000', 1) 
ALTER FUNCTION [dbo].[fn_dtFromEcmaTimeStamp](@in_timestamp bigint, @in_convert_to_localtime bit)
RETURNS datetime 
AS
BEGIN
    DECLARE @dtReturnValue AS datetime      
    DECLARE @hours int 
    SET @hours = @in_timestamp /(1000*60*60);

    DECLARE @milliseconds int 
    SET @milliseconds = @in_timestamp - (@in_timestamp /(1000*60*60))*(1000*60*60);


    SET @dtReturnValue = DATEADD 
                        (
                            MILLISECOND, @milliseconds, 
                            DATEADD(hour, @hours, CAST('19700101' AS datetime)) 
                        ) 


    IF @in_convert_to_localtime = 1 
    BEGIN
        SET @dtReturnValue = DATEADD(HOUR, 1, @dtReturnValue) 
        SET @dtReturnValue = 
                                CASE WHEN dbo.fn_dtIsCEST(@dtReturnValue) = 1 
                                    THEN DATEADD(HOUR, 1, @dtReturnValue) 
                                    ELSE @dtReturnValue 
                                END;
    END 

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "04_fn_dtFromEcmaTimeStamp.sql"' 


GO 

Answer 1

Since I wasn't previously aware of the SQL Server 2008 requirement, I've added a segment about SQL Server 2008 here. I still endorse everything in my original review, and strongly prefer that method.

Overall, I think the premise of the original review is still the best approach:

1. Convert to datetimeoffset with the appropriate time zone
2. Use that everywhere instead of a raw datetime

We still have functionality available to us in SQL Server 2008, but not as convenient as AT TIME ZONE

1. SWITCHOFFSET
2. TODATETIMEOFFSET

To use this, I would reformulate the problem into a few steps:

1. Determine the appropriate time zone of the data
2. Convert the data to be time-zone aware using TODATETIMEOFFSET
3. Swap to UTC using SWITCHOFFSET
4. Get your timestamp

When getting a datetime from the timestamp, it'll largely be the same in reverse.

In terms of where you should change things, I think it would look like this:

1. Update dbo.fn_dtLastSundayInMonth to return a date instead of a datetime (b/c we don't have any meaningful time information).
2. Update dbo.fn_dtIsCEST to return the offset instead of just a bit flag.
3. Add a new function, dbo.fn_dtToOffset that takes a date, derives the offset, and converts it using TODATETIMEOFFSET
4. Update dbo.fn_dtToEcmaTimestamp and inverse to either consume an offset or convert it inside, and use SWITCHOFFSET as needed if UTC is desired.

Otherwise my same general comments apply from the previous review.

I do think that, given the additional complexity vs AT TIME ZONE, you should consider converting your data to include the values as a datetimeoffset. This could either be a computed column (absolutely don't put a UDF in there, as all queries touching the table will tank), or creating a new column entirely, and then gradually transition your applications to use the new column insead of the old one.

---

Note - the rest of this answer assumes that you can use SQL Server 2016+ features.

You are overcomplicating this problem. There are only two pieces of functionality you need to solve this problem:

1. datetimeoffset
2. AT TIME ZONE

Using datetimeoffset lets you encode the timezone as part of the data. Then you can ignore the complexities of time zone math, and use standard APIs.

AT TIME ZONE lets you take an arbitrary datetime or datetimeoffset, and returns a datetimeoffset of the targetted time zone. Two main points here:

1. If the input does not have time zone information, then it will assume the target time zone. It will handle DST if necessary (see the docs for your example specifically)
2. If the input does have time zone information, then it will do whatever time zone math necessary to convert.

This turns your operations into:

DECLARE @dtDateTimeInLocal datetimeoffset = @in_DateTime AT TIME ZONE 'CENTRAL EUROPEAN STANDARD TIME';
DECLARE @dtDateTimeUtc datetimeoffset = @dtDateTimeInLocal AT TIME ZONE 'UTC';
DECLARE @timestamp bigint = dbo.fn_dtToEcmaTimestamp( @dtDateTimeUtc );
RETURN @timestamp;

If manually converting things to UTC is too much to ask of your users, then dbo.fn_dtToEcmaTimestamp can do the AT TIME ZONE 'UTC' and assume that the caller has a time-zone aware value.

If all of your data is already in CEST, you could also update the tables to be datetimeoffset using a similar method. Then moving forward you can always add data to be timezone aware, and this problem goes away.

A few final notes:

1. You should consider not using scalar UDFs, as they are the root of all evil. Use inline table-valued functions instead.
2. If they are necessary for <<reasons>>, then you should create them WITH SCHEMABINDING for performance reasons
3. I strongly prefer CREATE OR ALTER instead of this dynamic stuff you have going on with an ALTER later
4. Instead of leaving test cases as comments, investigate a framework like tSQLt to write unit tests in. This will make it much easier to do things like verify that my proposed solution doesn't change the behavior.
5. In dbo.fn_dtLastSundayInMonth, you can just use EOMONTH to simplify it a lot
6. In dbo.fn_dtLastSundayInMonth, you are overcomplicating your modular arithmatic.