Query MST instead of UTC for timestamps

Question

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.

Answer 1

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.