datetime 을 datetimeoffset 변환하기

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.