2

Anyway to convert from BST datetime stored in DB to UTC

Had a look at AT TIME ZONE but seems its from UTC to other times.

DB stores datetime as BST/GMT historically need to now return these as UTC

SELECT CONVERT(datetime,GETDATE()) AT TIME ZONE 'UTC' 

e.g

2019-07-08 12:01:55.060 ->  2019-07-08 11:01:550

Need to go back through loads of historic date data so could be GMT or BST depending on the time of the year and what year will determine cut over dates going back

Example below, i have the GMT/BST timezone times stored need them in UTC

DECLARE @dayLight TABLE(season VARCHAR(50), [gmt/bst] datetime, uct Datetime) 
INSERT INTO  @dayLight
VALUES('winter','2019-02-10 15:00:00','2019-02-10 15:00:00')
       ,('winter','2019-03-30 15:00:00','2019-03-30 15:00:00')
       ,('summer','2019-03-31 02:00:00','2019-03-31 01:00:00') --cut over
       ,('summer','2019-04-15 15:00:00','2019-03-30 14:00:00')
       ,('summer','2019-10-27 02:00:00','2019-10-27 01:00:00') --cut over
       ,('winter','2019-10-27 03:00:00','2019-10-27 03:00:00')

SELECT season,uct,[gmt/bst] FROM @dayLight     
4

Double up your AT TIME ZONE clauses to get what you want:

The first AT TIME ZONE clause specifies the current time zone you wish the value to be in, the second represents the time zone you wish to convert it to:

DECLARE @dayLight TABLE(season VARCHAR(50), [gmt/bst] datetime, utc Datetime) 
INSERT INTO  @dayLight
VALUES('winter','2019-02-10 15:00:00','2019-02-10 15:00:00')
       ,('winter','2019-03-30 15:00:00','2019-03-30 15:00:00')
       ,('summer','2019-03-31 02:00:00','2019-03-31 01:00:00') --cut over
       ,('summer','2019-04-15 15:00:00','2019-03-30 14:00:00')
       ,('summer','2019-10-27 01:59:59','2019-10-27 00:59:59') --cut over
       ,('winter','2019-10-27 03:00:00','2019-10-27 03:00:00')

SELECT season,utc,[gmt/bst]
        , [gmt/bst] AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC' AS [gmt/bst -> utc]
        , [utc] AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' AS [utc -> gmt/bst]
FROM @dayLight  

db<>fiddle

NOTE: I updated the October cut over time going back to standard time to 1:59:59am from 2:00am as the 2:00am time marks the point BST returns to standard time from Daylight Saving Time, so at that point, Daylight Saving Time is no longer in effect (contrary to your example).

5
  • great so TIMEZONE 'GMT Standard Time' handles the BST shifts? – davey Jul 8 '19 at 14:01
  • @davey, Per my testing it looks like it does. – John Eisbrener Jul 8 '19 at 14:03
  • Thats awesome i tried this originally but used AT TIME ZONE 'Greenwich Standard Time' AT TIME ZONE 'UTC'; GMT works though! thankyou! – davey Jul 8 '19 at 14:08
  • It's "Daylight Saving Time" (or even "Daylight-saving Time", although that's arguably archaic). We do not put daylight into a savings account and withdraw it months later. We save daylight every day by getting out of bed an hour earlier. – Monty Harder Jul 8 '19 at 20:28
  • @MontyHarder What?!?! We can't accrue interest on that extra hour? Thanks for the correction on what is a common misuse here in the USA! – John Eisbrener Jul 9 '19 at 3:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.