tag:blogger.com,1999:blog-4616149892194368111.post7431500182568787640..comments2024-03-28T00:27:54.968-07:00Comments on My Humble SQL Tips: Calculating British Summer Time (BST)Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-4616149892194368111.post-74882029851450533082020-10-22T06:06:30.191-07:002020-10-22T06:06:30.191-07:00the clocks go back at 02:00 not 01:00 when changin...the clocks go back at 02:00 not 01:00 when changing back to GMT ...<br />Schnofhttps://www.blogger.com/profile/14046085579562260227noreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-90920701282147674082020-05-05T07:50:59.276-07:002020-05-05T07:50:59.276-07:00this code doesn't work for 2021-10-31 :(
gutt...this code doesn't work for 2021-10-31 :( <br />gutted.. Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-15159150642665294762018-08-14T02:16:38.177-07:002018-08-14T02:16:38.177-07:00Typo in the 2021 Test should read:
--2021
select [...Typo in the 2021 Test should read:<br />--2021<br />select [dbo].[udf_bst_dates] ('2021-03-28 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2021-03-28 01:00:00') ;<br />select [dbo].[udf_bst_dates] ('2021-10-31 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2021-10-31 01:00:00') ;<br />Anonymoushttps://www.blogger.com/profile/00964397405079917834noreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-46266630251392030862018-08-14T02:13:42.878-07:002018-08-14T02:13:42.878-07:00The following table lists recent-past and near-fut...The following table lists recent-past and near-future start and end dates of British Summer Time:[5]<br /><br />Year Start End<br />2015 29 March 25 October<br />2016 27 March 30 October<br />2017 26 March 29 October<br />2018 25 March 28 October<br />2019 31 March 27 October<br />2020 29 March 25 October<br />2021 28 March 31 October<br /><br />Testing (all checks out good)<br /><br />--2015<br />select [dbo].[udf_bst_dates] ('2015-03-29 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2015-03-29 01:00:00') ;<br />select [dbo].[udf_bst_dates] ('2015-10-25 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2015-10-25 01:00:00') ;<br />--2016<br />select [dbo].[udf_bst_dates] ('2016-03-27 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2016-03-27 01:00:00') ;<br />select [dbo].[udf_bst_dates] ('2016-10-30 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2016-10-30 01:00:00') ;<br />--2017<br />select [dbo].[udf_bst_dates] ('2017-03-26 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2017-03-26 01:00:00') ;<br />select [dbo].[udf_bst_dates] ('2017-10-29 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2017-10-29 01:00:00') ;<br />--2018<br />select [dbo].[udf_bst_dates] ('2018-03-25 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2018-03-25 01:00:00') ;<br />select [dbo].[udf_bst_dates] ('2018-10-28 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2018-10-28 01:00:00') ;<br />--2019<br />select [dbo].[udf_bst_dates] ('2019-03-31 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2019-03-31 01:00:00') ;<br />select [dbo].[udf_bst_dates] ('2019-10-27 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2019-10-27 01:00:00') ;<br />--2020<br />select [dbo].[udf_bst_dates] ('2020-03-29 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2020-03-29 01:00:00') ;<br />select [dbo].[udf_bst_dates] ('2020-10-25 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2020-10-25 01:00:00') ;<br />--2021<br />select [dbo].[udf_bst_dates] ('2021-03-28 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2021-03-28 01:00:00') ;<br />select [dbo].[udf_bst_dates] ('2021-10-21 00:59:59') ;<br />select [dbo].[udf_bst_dates] ('2021-10-21 01:00:00') ;Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-84896502479110394232018-08-14T02:06:09.823-07:002018-08-14T02:06:09.823-07:00Surely the end time is 01:00:00 the clocks move ba...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...<br /><br />CREATE FUNCTION [dbo].[udf_bst_dates] ( @date datetime )<br />RETURNS BIT<br />AS<br />BEGIN<br /><br />DECLARE @rtn BIT<br />DECLARE @bst_start DATETIME<br />DECLARE @bst_end DATETIME<br />DECLARE @date_first INT<br />DECLARE @offset INT<br /><br />SET @date_first = @@DATEFIRST<br /><br />-- clocks move forward an hour on the last Sunday of March<br />-- and move back again on the last Sunday of October<br />SET @bst_start = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-03' + '-31T01:00:00.000' AS DATETIME)<br />SET @offset = ( DATEPART(dw, @bst_start) + @date_first - 1 ) % 7<br />SET @bst_start = DATEADD(dd, -@offset, @bst_start)<br /><br />SET @bst_end = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-10' + '-31T01:00:00' AS DATETIME)<br />SET @offset = ( DATEPART(dw, @bst_end) + @date_first - 1 ) % 7<br />SET @bst_end = DATEADD(dd, -@offset, @bst_end)<br /><br />IF @date >= @bst_start<br />AND @date < @bst_end<br />SET @rtn = 1<br />ELSE<br />SET @rtn = 0<br /><br />RETURN (@rtn)<br />END<br />Anonymoushttps://www.blogger.com/profile/00964397405079917834noreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-46198917864578955672018-06-26T03:23:12.034-07:002018-06-26T03:23:12.034-07:00Thanks, its working fine for me.Thanks, its working fine for me.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-22393408492659464652018-03-23T07:18:23.849-07:002018-03-23T07:18:23.849-07:00THanks, really helpful :) THanks, really helpful :) Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-9569421191578253732018-02-26T02:13:53.523-08:002018-02-26T02:13:53.523-08:00I had to add a 'T' between the date and ti...I had to add a 'T' between the date and time parts of the date string to prevent conversion errors, full code as follows:<br /><br />CREATE FUNCTION [dbo].[udf_bst_dates] ( @date datetime )<br />RETURNS BIT<br />AS<br />BEGIN<br /><br /> DECLARE @rtn BIT<br /> DECLARE @bst_start DATETIME<br /> DECLARE @bst_end DATETIME<br /> DECLARE @date_first INT<br /> DECLARE @offset INT<br /><br /> SET @date_first = @@DATEFIRST<br /><br /> -- clocks move forward an hour on the last Sunday of March<br /> -- and move back again on the last Sunday of October<br /> SET @bst_start = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-03' + '-31T01:00:00.000' AS DATETIME)<br /> SET @offset = ( DATEPART(dw, @bst_start) + @date_first - 1 ) % 7<br /> SET @bst_start = DATEADD(dd, -@offset, @bst_start)<br /><br /> SET @bst_end = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-10' + '-31T00:59:59' AS DATETIME)<br /> SET @offset = ( DATEPART(dw, @bst_end) + @date_first - 1 ) % 7<br /> SET @bst_end = DATEADD(dd, -@offset, @bst_end)<br /><br /> IF @date >= @bst_start<br /> AND @date < @bst_end<br /> SET @rtn = 1<br /> ELSE<br /> SET @rtn = 0<br /><br />RETURN (@rtn)<br />ENDAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-81028931013283384082017-05-10T02:34:04.223-07:002017-05-10T02:34:04.223-07:00LESS THAN OR EQUAL TO @bst_end ??LESS THAN OR EQUAL TO @bst_end ??Jamie Stevensonnoreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-35591489496409984642017-04-01T05:21:31.398-07:002017-04-01T05:21:31.398-07:00Awesome code and still useful in 2017 - thanks! Bu...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.<br /><br />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.<br /><br />A simple fix is to amend the times of the function...<br />Line 16: <b>+ '-31' AS DATETIME)</b><br />becomes:<br /> <b>+ '-31 01:00:00.000' AS DATETIME)</b><br /><br />and Line 21: <b>+ '-31 23:59:59' AS DATETIME)</b><br />becomes:<br /> <b>+ '-31 00:59:59' AS DATETIME)</b><br /><br />The full amended code is below for any lazy copy and pasters (myself included!) :-)<br /><br />--------------------------<br /><br />CREATE FUNCTION [dbo].[udf_bst_dates] ( @date datetime )<br />RETURNS BIT<br />AS<br /> BEGIN<br /><br /> DECLARE @rtn BIT<br /> DECLARE @bst_start DATETIME<br /> DECLARE @bst_end DATETIME<br /> DECLARE @date_first INT<br /> DECLARE @offset INT<br /><br /> SET @date_first = @@DATEFIRST<br /><br />-- clocks move forward an hour on the last Sunday of March<br />-- and move back again on the last Sunday of October<br /> SET @bst_start = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-03' + '-31 01:00:00.000' AS DATETIME)<br /> SET @offset = ( DATEPART(dw, @bst_start) + @date_first - 1 ) % 7<br /> SET @bst_start = DATEADD(dd, -@offset, @bst_start)<br /><br /> SET @bst_end = CAST(CAST(YEAR(@date) AS VARCHAR(4)) + '-10'<br /> + '-31 00:59:59' AS DATETIME)<br /> SET @offset = ( DATEPART(dw, @bst_end) + @date_first - 1 ) % 7<br /> SET @bst_end = DATEADD(dd, -@offset, @bst_end)<br /><br /> IF @date >= @bst_start<br /> AND @date < @bst_end<br /> SET @rtn = 1<br /> ELSE<br /> SET @rtn = 0<br /><br /> RETURN (@rtn)<br /><br /> ENDShamnoreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-88393671583956630392016-11-17T04:25:14.232-08:002016-11-17T04:25:14.232-08:00Great post, thankyou! I had to adjust the formula...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!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-69585589968069641402012-10-23T03:44:53.114-07:002012-10-23T03:44:53.114-07:00Thanks Howard. I didn't know about the @@DATEF...Thanks Howard. I didn't know about the @@DATEFIRST function. I'll give it a go.Thomas Muchahttps://www.blogger.com/profile/06864799585515493782noreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-15042751900720261162012-07-12T03:02:49.279-07:002012-07-12T03:02:49.279-07:00Remember you also need to take into account the @@...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):<br /><br />CREATE FUNCTION [dbo].[udf_bst_dates] (@date datetime)<br />RETURNS BIT<br />AS<br /><br />BEGIN<br /><br />DECLARE @rtn bit<br />DECLARE @bst_start datetime<br />DECLARE @bst_end datetime<br />DECLARE @date_first int<br />DECLARE @offset int<br /><br />SET @date_first = @@DATEFIRST<br /><br />-- clocks move forward an hour on the last Sunday of March<br />-- and move back again on the last Sunday of October<br />SET @bst_start = CAST(CAST(YEAR(@date) AS varchar(4))+'-03'+ '-31' AS datetime)<br />SET @offset = (DATEPART(dw, @bst_start) + @date_first - 1) % 7<br />SET @bst_start = DATEADD(dd, - @offset, @bst_start)<br /><br />SET @bst_end = CAST(CAST(YEAR(@date) AS varchar(4))+'-10'+ '-31 23:59:59' AS datetime)<br />SET @offset = (DATEPART(dw, @bst_end) + @date_first - 1) % 7<br />SET @bst_end = DATEADD(dd, - @offset, @bst_end)<br /><br />IF @date >= @bst_start AND @date <= @bst_end<br />SET @rtn = 1<br />ELSE<br />SET @rtn = 0<br /><br />RETURN (@rtn)<br /><br />ENDHowardhttps://www.blogger.com/profile/03029413747292078256noreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-77859416858831410432012-07-12T02:53:29.151-07:002012-07-12T02:53:29.151-07:00This comment has been removed by the author.Howardhttps://www.blogger.com/profile/03029413747292078256noreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-63900165438655129972012-06-07T08:39:21.070-07:002012-06-07T08:39:21.070-07:00This comment has been removed by the author.Thomas Muchahttps://www.blogger.com/profile/06864799585515493782noreply@blogger.comtag:blogger.com,1999:blog-4616149892194368111.post-73319230641115309652012-02-21T15:20:28.040-08:002012-02-21T15:20:28.040-08:00This might work a bit faster for UK
ALTER FUNCTI...This might work a bit faster for UK<br /><br /><br />ALTER FUNCTION [dbo].[udf_bst_dates] (@date DATE)<br />RETURNS BIT <br />AS <br />BEGIN <br /><br />DECLARE @rtn BIT <br />DECLARE @month INT <br />DECLARE @day INT <br />DECLARE @startdate VARCHAR (20)<br />--DECLARE @date date <br />DECLARE @end date <br />DECLARE @bst_start date<br />DECLARE @bst_end date<br /><br />--SET @date = '20120301'<br /><br />--Clocks move forward and hour on the last Sunday of March<br />--and move back again on the last Sunday of October.<br />SET @bst_start =Cast(Cast(Year(@date) as varchar(4))+'03'+ '31' as DATE)<br />SET @bst_start = dateadd(dd,-1*Datepart(dw,@bst_start),@bst_start)<br />--SELECT Datepart(dw,@bst_start)<br />--SELECT Datename(dw,@bst_start)<br /><br />SET @bst_end =Cast(Cast(Year(@date) as varchar(4))+'10'+ '31' as DATE)<br />SET @bst_end = dateadd(dd,-1*Datepart(dw,@bst_end),@bst_end)<br />--SELECT Datepart(dw,@bst_end)<br />--SELECT Datename(dw,@bst_end)<br /><br />IF @date >= @bst_start<br />AND @date <= @bst_end<br />SET @rtn = 1<br />ELSE <br />SET @rtn = 0<br />--SELECT @rtn<br />RETURN (@rtn)<br />ENDDarrenhttps://www.blogger.com/profile/14355316307490829158noreply@blogger.com