I ran into a problem where time sensitive data was being sent to Sales staff. Our servers are permanently set to Greenwich Mean Time (GMT) to avoid problems with scheduled tasks on the days when the clocks change. Sales staff, however were continually baffled by the GMT times when we were in BST time. So I wrote a User Defined Scalar Function to return an bit value indicating whether a date falls within BST or not.
The function returns 1 if the date falls within BSTThe function returns 0 if the date falls outside BST
BST is the same as European Summer Time. Clocks move forward and hour on the last Sunday of March and move back again on the last Sunday of October.
I've modified this function to calculate Daylight Saving Time in the United States (USA) - See below. Clocks move forward and hour on the second Sunday of March and move back again on the first Sunday of November.
UPDATE: Based on the comment from Howard (see below): Please use this script:
/*****************************
My old and clumsy script:
*****************************/
Britain and Europe:
United States:
Example of usage:
This might work a bit faster for UK
ReplyDeleteALTER FUNCTION [dbo].[udf_bst_dates] (@date DATE)
RETURNS BIT
AS
BEGIN
DECLARE @rtn BIT
DECLARE @month INT
DECLARE @day INT
DECLARE @startdate VARCHAR (20)
--DECLARE @date date
DECLARE @end date
DECLARE @bst_start date
DECLARE @bst_end date
--SET @date = '20120301'
--Clocks move forward and hour on the last Sunday of March
--and move back again on the last Sunday of October.
SET @bst_start =Cast(Cast(Year(@date) as varchar(4))+'03'+ '31' as DATE)
SET @bst_start = dateadd(dd,-1*Datepart(dw,@bst_start),@bst_start)
--SELECT Datepart(dw,@bst_start)
--SELECT Datename(dw,@bst_start)
SET @bst_end =Cast(Cast(Year(@date) as varchar(4))+'10'+ '31' as DATE)
SET @bst_end = dateadd(dd,-1*Datepart(dw,@bst_end),@bst_end)
--SELECT Datepart(dw,@bst_end)
--SELECT Datename(dw,@bst_end)
IF @date >= @bst_start
AND @date <= @bst_end
SET @rtn = 1
ELSE
SET @rtn = 0
--SELECT @rtn
RETURN (@rtn)
END
This comment has been removed by the author.
DeleteThis comment has been removed by the author.
DeleteRemember you also need to take into account the @@DATEFIRST setting and time elements. This mod of Darren's work looks right to me (SQL Server 7):
DeleteCREATE FUNCTION [dbo].[udf_bst_dates] (@date datetime)
RETURNS BIT
AS
BEGIN
DECLARE @rtn bit
DECLARE @bst_start datetime
DECLARE @bst_end datetime
DECLARE @date_first int
DECLARE @offset int
SET @date_first = @@DATEFIRST
-- clocks move forward an hour on the last Sunday of March
-- and move back again on the last Sunday of October
SET @bst_start = CAST(CAST(YEAR(@date) AS varchar(4))+'-03'+ '-31' AS datetime)
SET @offset = (DATEPART(dw, @bst_start) + @date_first - 1) % 7
SET @bst_start = DATEADD(dd, - @offset, @bst_start)
SET @bst_end = CAST(CAST(YEAR(@date) AS varchar(4))+'-10'+ '-31 23:59:59' AS datetime)
SET @offset = (DATEPART(dw, @bst_end) + @date_first - 1) % 7
SET @bst_end = DATEADD(dd, - @offset, @bst_end)
IF @date >= @bst_start AND @date <= @bst_end
SET @rtn = 1
ELSE
SET @rtn = 0
RETURN (@rtn)
END
Thanks Howard. I didn't know about the @@DATEFIRST function. I'll give it a go.
ReplyDeleteGreat post, thankyou! I had to adjust the formula for my collation, the '-03' and '-10' text was replaced with '-mar' and '-oct' respectively. worked like a charm!
ReplyDeleteAwesome code and still useful in 2017 - thanks! But I did find a bug. This bug is around the exact timing of BST start and end. The code assumes this happens at midnight but infact the times change at 1am GMT thus resulting in incorrect datetime ajustments using this function if the datetime falls between the date of change but a time between midnight to 1am GMT.
ReplyDeleteE.g. on Sun 26th March 2017 at 12.30 am, the bug version would return 1, however it should return 0 as BST is not in effect for another half hour. For the end of BST the time bug window to get an incorrect result is much greater.
A simple fix is to amend the times of the function...
Line 16: + '-31' AS DATETIME)
becomes:
+ '-31 01:00:00.000' AS DATETIME)
and Line 21: + '-31 23:59:59' AS DATETIME)
becomes:
+ '-31 00:59:59' AS DATETIME)
The full amended code is below for any lazy copy and pasters (myself included!) :-)
--------------------------
CREATE FUNCTION [dbo].[udf_bst_dates] ( @date datetime )
RETURNS BIT
AS
BEGIN
DECLARE @rtn BIT
DECLARE @bst_start DATETIME
DECLARE @bst_end DATETIME
DECLARE @date_first INT
DECLARE @offset INT
SET @date_first = @@DATEFIRST
-- clocks move forward an hour on the last Sunday of March
-- and move back again on the last Sunday of October
SET @bst_start = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-03' + '-31 01:00:00.000' AS DATETIME)
SET @offset = ( DATEPART(dw, @bst_start) + @date_first - 1 ) % 7
SET @bst_start = DATEADD(dd, -@offset, @bst_start)
SET @bst_end = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-10'
+ '-31 00:59:59' AS DATETIME)
SET @offset = ( DATEPART(dw, @bst_end) + @date_first - 1 ) % 7
SET @bst_end = DATEADD(dd, -@offset, @bst_end)
IF @date >= @bst_start
AND @date < @bst_end
SET @rtn = 1
ELSE
SET @rtn = 0
RETURN (@rtn)
END
LESS THAN OR EQUAL TO @bst_end ??
ReplyDeleteI had to add a 'T' between the date and time parts of the date string to prevent conversion errors, full code as follows:
ReplyDeleteCREATE FUNCTION [dbo].[udf_bst_dates] ( @date datetime )
RETURNS BIT
AS
BEGIN
DECLARE @rtn BIT
DECLARE @bst_start DATETIME
DECLARE @bst_end DATETIME
DECLARE @date_first INT
DECLARE @offset INT
SET @date_first = @@DATEFIRST
-- clocks move forward an hour on the last Sunday of March
-- and move back again on the last Sunday of October
SET @bst_start = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-03' + '-31T01:00:00.000' AS DATETIME)
SET @offset = ( DATEPART(dw, @bst_start) + @date_first - 1 ) % 7
SET @bst_start = DATEADD(dd, -@offset, @bst_start)
SET @bst_end = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-10' + '-31T00:59:59' AS DATETIME)
SET @offset = ( DATEPART(dw, @bst_end) + @date_first - 1 ) % 7
SET @bst_end = DATEADD(dd, -@offset, @bst_end)
IF @date >= @bst_start
AND @date < @bst_end
SET @rtn = 1
ELSE
SET @rtn = 0
RETURN (@rtn)
END
THanks, really helpful :)
ReplyDeleteThanks, its working fine for me.
ReplyDeleteSurely the end time is 01:00:00 the clocks move back one hour at 1am on the last Sunday of each month not at 00:59:59...
ReplyDeleteCREATE FUNCTION [dbo].[udf_bst_dates] ( @date datetime )
RETURNS BIT
AS
BEGIN
DECLARE @rtn BIT
DECLARE @bst_start DATETIME
DECLARE @bst_end DATETIME
DECLARE @date_first INT
DECLARE @offset INT
SET @date_first = @@DATEFIRST
-- clocks move forward an hour on the last Sunday of March
-- and move back again on the last Sunday of October
SET @bst_start = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-03' + '-31T01:00:00.000' AS DATETIME)
SET @offset = ( DATEPART(dw, @bst_start) + @date_first - 1 ) % 7
SET @bst_start = DATEADD(dd, -@offset, @bst_start)
SET @bst_end = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-10' + '-31T01:00:00' AS DATETIME)
SET @offset = ( DATEPART(dw, @bst_end) + @date_first - 1 ) % 7
SET @bst_end = DATEADD(dd, -@offset, @bst_end)
IF @date >= @bst_start
AND @date < @bst_end
SET @rtn = 1
ELSE
SET @rtn = 0
RETURN (@rtn)
END
The following table lists recent-past and near-future start and end dates of British Summer Time:[5]
ReplyDeleteYear Start End
2015 29 March 25 October
2016 27 March 30 October
2017 26 March 29 October
2018 25 March 28 October
2019 31 March 27 October
2020 29 March 25 October
2021 28 March 31 October
Testing (all checks out good)
--2015
select [dbo].[udf_bst_dates] ('2015-03-29 00:59:59') ;
select [dbo].[udf_bst_dates] ('2015-03-29 01:00:00') ;
select [dbo].[udf_bst_dates] ('2015-10-25 00:59:59') ;
select [dbo].[udf_bst_dates] ('2015-10-25 01:00:00') ;
--2016
select [dbo].[udf_bst_dates] ('2016-03-27 00:59:59') ;
select [dbo].[udf_bst_dates] ('2016-03-27 01:00:00') ;
select [dbo].[udf_bst_dates] ('2016-10-30 00:59:59') ;
select [dbo].[udf_bst_dates] ('2016-10-30 01:00:00') ;
--2017
select [dbo].[udf_bst_dates] ('2017-03-26 00:59:59') ;
select [dbo].[udf_bst_dates] ('2017-03-26 01:00:00') ;
select [dbo].[udf_bst_dates] ('2017-10-29 00:59:59') ;
select [dbo].[udf_bst_dates] ('2017-10-29 01:00:00') ;
--2018
select [dbo].[udf_bst_dates] ('2018-03-25 00:59:59') ;
select [dbo].[udf_bst_dates] ('2018-03-25 01:00:00') ;
select [dbo].[udf_bst_dates] ('2018-10-28 00:59:59') ;
select [dbo].[udf_bst_dates] ('2018-10-28 01:00:00') ;
--2019
select [dbo].[udf_bst_dates] ('2019-03-31 00:59:59') ;
select [dbo].[udf_bst_dates] ('2019-03-31 01:00:00') ;
select [dbo].[udf_bst_dates] ('2019-10-27 00:59:59') ;
select [dbo].[udf_bst_dates] ('2019-10-27 01:00:00') ;
--2020
select [dbo].[udf_bst_dates] ('2020-03-29 00:59:59') ;
select [dbo].[udf_bst_dates] ('2020-03-29 01:00:00') ;
select [dbo].[udf_bst_dates] ('2020-10-25 00:59:59') ;
select [dbo].[udf_bst_dates] ('2020-10-25 01:00:00') ;
--2021
select [dbo].[udf_bst_dates] ('2021-03-28 00:59:59') ;
select [dbo].[udf_bst_dates] ('2021-03-28 01:00:00') ;
select [dbo].[udf_bst_dates] ('2021-10-21 00:59:59') ;
select [dbo].[udf_bst_dates] ('2021-10-21 01:00:00') ;
Typo in the 2021 Test should read:
ReplyDelete--2021
select [dbo].[udf_bst_dates] ('2021-03-28 00:59:59') ;
select [dbo].[udf_bst_dates] ('2021-03-28 01:00:00') ;
select [dbo].[udf_bst_dates] ('2021-10-31 00:59:59') ;
select [dbo].[udf_bst_dates] ('2021-10-31 01:00:00') ;
this code doesn't work for 2021-10-31 :(
ReplyDeletegutted..
the clocks go back at 02:00 not 01:00 when changing back to GMT ...
ReplyDelete