SQL Server Timestamps – Query MST Instead of UTC

sql servertimestamputc-time

on SQL Server, i'm trying to query all of last months data based off of the time stamp, however, all those timestamps are in UTC and i need them to be in MST (the reason is mainly for the end dates)

So far this is what I have:

Select Vehicle.Timestamp
WHERE Vehicle.Timestamp >= DATEADD(mm,DATEDIFF(mm,32,GETUTCDATE()),0) 
AND Vehicle.Timestamp < DATEADD(mm,DATEDIFF(mm,0,GETUTCDATE()),0) 
order by Vehicle.Timestamp

i'm not entirely sure which way to move forward.

I've seen the use of switchoffset however, not sure the best way to apply it.

Best Answer

If you just need this for a one-time query, I'd recommend something like this:

WITH AdjustedVehicleInfo AS
(
    SELECT DATEADD(HOUR, -7, Timestamp) AS MST_Timestamp 
    FROM Vehicle
)
SELECT MST_Timestamp
FROM AdjustedVehicleInfo
WHERE MST_Timestamp BETWEEN @StartDate AND @EndDate

where @StartDate and @EndDate are set to MST times.

The reason I put that disclaimer is because the offset between GETDATE() and GETUTCDATE() is not always -7 for MST, and indeed is not the same across all timeframes for most timezones (places like Arizona and other non-DST-observant localities being exceptions). If you want this to become a query that people/software is going to use for a long time, you'll need to figure out a different way of adjusting the values to the desired timezone.

Related Question