SQLTeam.com | Weblogs | Forums

UTC to PST conversion and comparison


#1

Source Table date column is in UTC
System dateTime is PST

So if i need to compare like this , will it work

select * from source
where column > DateAdd(hour, -1, SourceDateTime_UTC)

Also how will i convert UTC time into PST time.


#2

Which version (year) of SQL Server are you on?


#3

Microsoft SQL Server 2016


#4

Great! That makes it so much easier. You can use the AT TIME ZONE clause to adjust the time to that zone.

Then you can add an hour, subtract an hour, whatever, from that result if/as you need to:

WHERE column AT TIME ZONE 'Pacific Standard Time' > DATEADD(HOUR, 1, SourceDateTime_UTC AT TIME ZONE 'UTC')


#5

When i convert it simply add offset

select getdate() AT TIME ZONE 'Pacific Standard Time'
2018-12-06 14:14:53.740 -08:00
How would i make it a real datetime value


#6

Sorry, I was focused on the comparison part.

To convert from one to the other, you can add the differential between the two time zones:

For example:
select dateadd(minute, datediff(minute, getdate() at time zone 'Pacific Standard Time', getdate() at time zone 'utc'), getdate())

select dateadd(minute, datediff(minute, order_date at time zone 'Pacific Standard Time', order_date at time zone 'utc'), order_date)