If I use the SQL Tile to pull in the results of a query containing a UTC date that I need to convert to the local time zone, I get some odd results. I’ve created a connection to the OperationsManager database for the below query, and it works fine:
SELECT ManagedTypePropertyName,
SettingValue,
mtv.DisplayName,
CONVERT(varchar(64),(gs.LastModified AT TIME ZONE ‘Pacific Standard Time’), 21) AS LastModified2016,
DATEADD(hh,(DATEDIFF(hh,getutcdate(),getdate())),gs.LastModified) AS LastModified,
gs.LastModified AS LastModifiedRaw
FROM GlobalSettings gs
INNER JOIN ManagedTypeProperty mtp on gs.ManagedTypePropertyId = mtp.ManagedTypePropertyId
INNER JOIN ManagedTypeView mtv on mtp.ManagedTypeId = mtv.Id
WHERE mtv.DisplayName <> ‘Crash Listener’
ORDER BY mtv.DisplayName
For testing purposes, I’m returning three different samples. Note that the value for gs.LastModified is stored in the database as a UTC time, and I am trying to convert it to local time.
In the two methods above, if I execute the query in SSMS, I get exactly what I would expect.
LastModified2016 returns the date with the time delta at the end instead of just the date.
LastModified returns the date with the date properly reflecting the local time.
LastModifiedRaw returns the date exactly as it is in the database.
The DB server, the system with SSMS, and the SquaredUp server are all in the same time zone, have the same system time, etc…
When I plug the query with the appropriate line of SQL in it into SquaredUp, LastModified2016 shows exactly the same date that LastModifiedRaw does, it just also displays the offset. It doesn’t calculate it. LastModified shows the date as -15 instead of -8 as it should. That is to say, if the hh is 22, SSMS shows the date minus 8 hours (14). SquaredUp shows the date minus 15 (07).
I’m using the moustache formatter {{timeago(value, true, true,true,false)}} but I don’t know why this would alter the datediff version and not the others. Any ideas? Anyone?
EDIT: I believe I’ve figured this out, and thought I wopuld share:
CONVERT(varchar(64),(DATEADD(hh,(DATEDIFF(hh,getutcdate(),getdate())),gs.LastModified)),21) AS LastModified
Tha’s a pain…