0

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.

LowlyDBA - John M
  • 11,059
  • 11
  • 45
  • 63
scaborski
  • 3
  • 4

1 Answers1

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.

nateirvin
  • 756
  • 1
  • 6
  • 22