Wednesday, April 12, 2017

converting local time to UTC time With daylight Savings

I have a job to get data from a table with 4 hours data..my column is datetime and UTC default. and i prepared some logic below...
SELECT *  FROM   CallHistory WITH(NOLOCK)
WHERE callplacedtimeUTC >=  DATEADD(hh,-4,GETUTCDATE())
GO

But it dint work with daylight savings...so, my boss asked me to convert local time to UTC time which will work and gave me below logic...
Just  add below in place of -4

SELECT DATEADD(HOUR, -1 * DATEDIFF(HOUR, GETDATE(), GETUTCDATE()), GETUTCDAT


SELECT *  FROM  CallHistory WITH(NOLOCK)
WHERE callplacedtimeUTC >=  DATEADD(hh,-1 * DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),GETUTCDATE())
GO


it works for daylight savings...

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...