UTC to PST conversion and comparison

Transact-SQL
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')

1 Like
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)

1 Like
7

Thanks for giving me valuable information

8

Here's how you can do it in SQL:
-- 1. Convert system date and time to UTC
DECLARE @SystemDateTime_PST DATETIME
SET @SystemDateTime_PST = GETDATE() -- Assuming GETDATE() returns PST time

-- 2. Compare with the source table
SELECT *
FROM source
WHERE column > DATEADD(HOUR, -8, @SystemDateTime_PST) -- Assuming PST is UTC-8

We declare a variable @SystemDateTime_PST to store the current system date and time in PST. You should ensure that GETDATE() or any equivalent function you are using returns the date and time in PST.