How to write an efficient query to compare data in a DATETIME column with a date from a different time zone?

Question

In a table with large number of rows, if a DATETIME column (ModifiedDate) stores data with the UTC time zone, how do I efficiently query the rows with ModifiedDate after 1/1/2023 of pacific time zone?

Answer 1

You may write something like this:

SELECT ModifiedDate,

ModifiedDate AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS ModifiedDateLocalTime

FROM <TableName>

WHERE ModifiedDate >= CAST('1/1/2023' AS DATETIME) AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC'

The WHERE clause above converts the local date 1/1/2023 from the Pacific time zone to UTC (i.e., 1/1/2023 8:00 AM at UTC) and compare it with the UTC value of ModifiedDate.

If the WHERE clause above is changed to:

WHERE ModifiedDate AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' >= '1/1/2023'

The query will return the same result but it's less efficient because every record needs to be converted to the Pacific time zone to compare with 1/1/2023.