CODEWITHSUNDEEP
sqlsql-server
antonio_mg
antonio_mg

Reputation: 492

SQL Find if two UTC Dates are in the same calendar day

I have a table with a DateTime column, this column contains a DateTime in UTC which in local time is always time 00:00:00.000, this is because I only care about Date part, not time but I need to store the Date in UTC.In my local time, the UTC offset is -6 hours, so, all my dates are stored with a time equals to 6, for example, '2017-01-03 06:00:00.000' . The problem is that when I try to compare this:

SELECT (CASE WHEN (CONVERT(DATE, '2017-01-03 06:00:00.000') = CONVERT(DATE, GETUTCDATE())) THEN 1 ELSE 0 END) AS [IsEqual]

If the result of GETUTCDATE is another day but the hour is smaller than 6, for example, '2017-01-04 02:00:00.000', this date in local time still belong to January 3, but the above comparison returns false because is checking if 2017-01-03 = 2017-01-04.

How can I find id this two UTC dates are on the same calendar day?

Upvotes: 0

Views: 403

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82514

If your local time is always 6 hours from UTC (meaning no Daylight saving time), you should subtract 6 hours from GETUTCDATE() and then compare to your local time:

SELECT CASE WHEN 
                CONVERT(DATE, '2017-01-03 06:00:00.000') = 
                CONVERT(DATE, DATEADD(HOUR, -6, GETUTCDATE())) THEN 
           1 
       ELSE 
           0 
       END AS [IsEqual]

Upvotes: 2

Related Questions