CODEWITHSUNDEEP
sql-servert-sqldatetimetimezone
JT-65
JT-65

Reputation: 11

TSQL Convert DateTime & Timezone Offset (in Minutes) to another Time Zone

In my SQL Server 2008 R2 database, I have a LastHWScan column (datetime) and a TimzoneOffset column (int).

The LastHWScan column stores the datetime that is local to the workstation being scanned. The TimezoneOffset column stores the timezone offset as either a positive or negative number of minutes. Dallas is -300, Bucharest is 180.

How can I, in a SQL query, convert these datetime from various timezones to Dallas time?

Would it be easier to convert everything to UTC and then convert it to Dallas time?

Upvotes: 1

Views: 2085

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241683

In my SQL Server 2008 R2 database, I have a LastHWScan field (datetime) and a TimzoneOffset field (int).

That's fine, though you might also consider that SQL Server has a datetimeoffset field which can combine these into a single type.

Or, if the time zone offset is unimportant to your scenario, you might consider storing just the equivalent UTC value in a datetime or datetime2 field.

The LastHWScan field stores the datetime that is local to the workstation being scanned. The TimezoneOffset field stores the timezone offset as either a positive or negative number of minutes. Dallas is -300, Bucharest is 180.

Be careful here. The offset should be aligned to the specific date and time. Dallas may be -300 (UTC-5) in the summer when DST is in effect, but most of the year it's -360 (UTC-6). Likewise, Bucharest alternates between 180 (UTC+3) and 120 (UTC+2).

Do not detach the offset from the date/time, or assume that the offset for one location and date/time is valid for all possible other date/time values within that location.

How can I, in a SQL query, convert these datetimes from various timezones to Dallas time?

That's difficult to do in SQL, because you have to account for the daylight saving time rules of Dallas. Other DBs have functions like CONVERT_TZ, but SQL Server doesn't have anything like that built in. You can write your own functions, or you might consider my SQL Server Time Zone Support project.

Would it be easier to convert everything to UTC and then convert it to Dallas time?

"Easier" is subjective. Many people prefer storing values in UTC. Storing them in a datetimeoffset (or in two fields like you have done) is fine - and it's preferable when the local time is important in the context of the application domain (See DateTime vs DateTimeOffset). The only thing that you shouldn't do is drop the offset and store only local time.

Even if you did convert to UTC, that wouldn't help with the conversion. Though you've properly identified a specific instant in time, you still have to account for time zone rules of the destination time zone. If you only need US Central time, you might consider hardcoding the transition rules and offsets into a UDF or stored proc. Otherwise, use my time zone integration project with the "America/Chicago" time zone id.

Example:

SELECT Tzdb.UtcToLocal(dateadd(mi, -offset, localdatetime), 'America/Chicago')

Note the negation of the offset to convert from local back to UTC before calling the custom UDF.

Upvotes: 1

Related Questions