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:

7 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