Ttykuu

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