DATEADD in SQL Server not accounting for day rollover on previous days

Question

I'm working with a SQL Server scenario wherein I have zero control over the structure. Data is being transferred between two servers via SSIS.

In one server, a table contains dates stored in UTC (good). In the other, older server, dates are stored at local time when the data was originally written for the server (ech).

The older server used to be the only one, which means some old apps consume data only from there, and need the data already in local time. The software cannot be replaced or updated at this time to handle its own conversions.

The problem is coming from the difference of UTC to local time. The versions of SQL Server being used don't have the functions newer versions have where you can "AT TIME ZONE" etc a date. This means that transforms all have to be done the old fashioned way. In the following query:

DATEADD(hour, (SELECT DISTICNT DATEDIFF(hour, CONVERT(time, GETUTCDATE()), CONVERT(time, SYSDATETIMEOFFSET())) 
               FROM ORIGIN_DATABASE.TABLE_DATA), 
              CONVERT(DATETIME2(0), [OLD_COLUMN_NAME])) AS NEW_COLUMN_NAME

I'm getting the difference between the current UTC date and the local system time, then adding that result to the time from the column in order to apply the offset (which happens to be a negative number). This works for the hours, but when the time would be reduced below 0:00 to the previous night, the date isn't rolling over.

From what I've read (which may be wrong as I do have some conflicting resources on this in particular), DATEADD in T-SQL should account for this.

What am I doing wrong?

Answer 1

The problem is that you are getting the difference in 2 time values, which don't have a date; they are (as the name suggests) a time.

Let's take some example data with some datetime2(0) and datetimeoffset(0) values:

CREATE TABLE dbo.SomeTable (UTCDateTime datetime2(0),
                            OffsetDateTime datetimeoffset(0));

INSERT INTO dbo.SomeTable (UTCDateTime,OffsetDateTime)
VALUES('2022-09-02T07:00:00','2022-09-02T08:00:00+01:00'),
      ('2022-09-02T00:59:06','2022-09-01T23:59:06-01:00'),
      ('2022-09-02T22:42:00','2022-09-03T04:42:00+06:00');

And now let's apply your solution:

SELECT DATEDIFF(HOUR, CONVERT(time(0), UTCDateTime), CONVERT(time(0), OffsetDateTime))
FROM dbo.SomeTable;

This results in these very wrong answers:

1
23
-18

The simplest way would be to get the timezone offset and then add that to the value. So, to convert UTC time to the offset time:

SELECT UTCDateTime,
       OffsetDateTime,
       DATEADD(MINUTE,DATEPART(TZOFFSET,OffsetDateTime),UTCDateTime),
       DATEPART(TZOFFSET,OffsetDateTime)
FROM dbo.SomeTable;

If you wanted to use your logic, you would need to use a date and time value, not a time value. I also suggest using minutes, not hours, as not all timezones are on the hour:

SELECT UTCDateTime,
       OffsetDateTime,
       DATEADD(MINUTE, DATEDIFF(MINUTE,UTCDateTime, CONVERT(datetime2(0),OffsetDateTime)), UTCDateTime) 
FROM dbo.SomeTable;

db<>fiddle

If you wanted to convert from the offset to UTC, you'd use similar logic but minus (-) the offset, not add it:

SELECT UTCDateTime,
       OffsetDateTime,
       DATEADD(MINUTE,-DATEPART(TZOFFSET,OffsetDateTime),CONVERT(datetime2(0),OffsetDateTime)),
       DATEPART(TZOFFSET,OffsetDateTime)
FROM dbo.SomeTable;

---

TL;DR: You want one of the following:

DATEADD(MINUTE,DATEPART(TZOFFSET,SYSDATETIMEOFFSET()),OLD_COLUMN_NAME) AS NEW_COLUMN_NAME

DATEADD(MINUTE, DATEDIFF(MINUTE,SYSDATETIME(), CONVERT(datetime2(0),SYSDATETIMEOFFSET())), OLD_COLUMN_NAME) AS NEW_COLUMN_NAME