http://stackoverflow.com/a/17867687
i figured it out. The trick is that there is a built-in SQL Server function ToDateTimeOffset, which attaches arbitrary offset information to any supplied datetime.
For example, the identical queries:
SELECT ToDateTimeOffset('2013-07-25 15:35:27', -240)
SELECT ToDateTimeOffset('2013-07-25 15:35:27', '-04:00')
both return:
2013-07-25 15:35:27.0000000 -04:00
Note: The offset parameter to ToDateTimeOffset can either be:
an integer, representing a number of minutes
a string, representing a hours and minutes (in {+|-}TZH:THM format)
We need the server's current UTC offset
Next we need the server's current offset from UTC. I are two ways i can have SQL Server return the the integer number of minutes we are from UTC:
DATEPART(TZOFFSET, SYSDATETIMEOFFSET())
DATEDIFF(minute, GETUTCDATE(), GETDATE())
both return
-240
Plugging this into the TODATETIMEOFFSET function:
SELECT ToDateTimeOffset(
'2013-07-25 15:35:27',
DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) --e.g. -240
)
returns the datetimeoffset value i want:
2013-07-25 15:35:27.0000000 -04:00
Putting it altogether
Now we can have a better function to convert a datetime into a datetimeoffset:
CREATE FUNCTION dbo.ToDateTimeOffset(@value datetime2)
RETURNS datetimeoffset AS
BEGIN
/*
Converts a date/time without any timezone offset into a datetimeoffset value,
using the server's current offset from UTC.
For this we use the builtin ToDateTimeOffset function;
which attaches timezone offset information with a datetimeoffset value.
The trick is to use DATEDIFF(minutes) between local server time and UTC
to get the offset parameter.
For example:
DATEPART(TZOFFSET, SYSDATETIMEOFFSET())
returns the integer
-240
for people in EDT (Eastern Daylight Time), which is 4 hours (240 minutes) behind UTC.
Pass that value to the SQL Server function:
TODATETIMEOFFSET(@value, -240)
*/
RETURN TODATETIMEOFFSET(@value, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()))
END;
Sample usage
SELECT TOP 5
ChangeDate,
dbo.ToDateTimeOffset(ChangeDate) AS ChangeDateOffset
FROM AuditLog
returns the desired:
ChangeDate ChangeDateOffset
======================= ==================================
2013-07-25 04:00:03.060 2013-07-25 04:00:03.0600000 -04:00
2013-07-24 04:00:03.073 2013-07-24 04:00:03.0730000 -04:00
2013-07-23 04:00:03.273 2013-07-23 04:00:03.2730000 -04:00
2013-07-20 04:00:02.870 2013-07-20 04:00:02.8700000 -04:00
2013-07-19 04:00:03.780 2013-07-19 04:00:03.7800000 -04:00
It would have been ideal if the built-in function would have just did this:
TODATETIMEOFFSET(value)
rather than having to create an "overload":
dbo.ToDateTimeOffset(value)
Note: Any code is released into the public domain. No attribution required.