0

Objective:

Knowing that my main column dates is in UTC time but is taking data from the Time zone 'Dateline Standard Time', how can I ensure that my query accounts for the offset without having to manually do DATEADD(HOUR,-8,column_name). This is especially important for day light savings periods.

select
    , created_at 
    , created_at at time zone 'Dateline Standard Time' as dates_zone

The output of this is: 

created_at                 created_at_timezone
2019-07-01 00:45:04.000    2019-07-01 00:45:04.000 -12:00

but I'd rather have the end result as such: (which is basically UTC-8)

created_at_modified            
2019-06-30 16:45:04.000
Dale K
  • 25,246
  • 15
  • 42
  • 71
Roger Steinberg
  • 1,554
  • 2
  • 18
  • 46

1 Answers1

0

You can convert the date to datetimeoffset:

SELECT CONVERT(datetimeoffset,created_at) AT TIME ZONE 'Dateline Standard Time' created_at_modified
FROM …
;
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • the issue i have is that the raw dates is GMT-12:00 International Date Line West. On the database, its -8:00 . Your formula reduces it by 12 hours instead of 8. (My dates on the database are UTC which is why its causing an 8 hour discrepancy – Roger Steinberg Jul 15 '19 at 21:58