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 * FROM CallHistory WITH(NOLOCK)
WHERE callplacedtimeUTC >= DATEADD(hh,-1 * DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),GETUTCDATE())
GO
it works for daylight savings...
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
WHERE callplacedtimeUTC >= DATEADD(hh,-1 * DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),GETUTCDATE())
GO
it works for daylight savings...
No comments:
Post a Comment