Friday 14 August 2009

Calculating British Summer Time (BST)

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 BST
The 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:

16 comments:

Darren said...

This might work a bit faster for UK


ALTER 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

Thomas Mucha said...
This comment has been removed by the author.
Howard said...
This comment has been removed by the author.
Howard said...

Remember 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):

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' 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

Thomas Mucha said...

Thanks Howard. I didn't know about the @@DATEFIRST function. I'll give it a go.

Anonymous said...

Great 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!

Sham said...

Awesome 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.

E.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

Jamie Stevenson said...

LESS THAN OR EQUAL TO @bst_end ??

Anonymous said...

I had to add a 'T' between the date and time parts of the date string to prevent conversion errors, full code as follows:

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' + '-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

Anonymous said...

THanks, really helpful :)

Anonymous said...

Thanks, its working fine for me.

Unknown said...

Surely 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...

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' + '-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

Anonymous said...

The following table lists recent-past and near-future start and end dates of British Summer Time:[5]

Year 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') ;

Unknown said...

Typo in the 2021 Test should read:
--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') ;

Anonymous said...

this code doesn't work for 2021-10-31 :(
gutted..

Schnof said...

the clocks go back at 02:00 not 01:00 when changing back to GMT ...