Menu
I'm hoping to convert a table which has a DATETIMEOFFSET field, down to a DATETIME field BUT recalculates the time by taking notice of the offset. This, in effect, converts the value to UTC.
eg.
CreatedOn: 2008-12-19 17:30:09.0000000 +11:00 that will get converted to
CreatedOn: 2008-12-19 06:30:09.0000000 or
CreatedOn: 2008-12-19 06:30:09.0000000 + 00:00 -- that's a `DATETIMEOFFSET`, but `UTC`. Cheers :)
514
In performing the conversion to local time, the method first converts the current DateTimeOffset object's date and time to Coordinated Universal Time (UTC) by subtracting the offset from the time. It then converts the UTC date and time to local time by adding the local time zone offset.
With its Kind property, DateTime is able to reflect only Coordinated Universal Time (UTC) and the system's local time zone. DateTimeOffset reflects a time's offset from UTC, but it does not reflect the actual time zone to which that offset belongs.
We can convert the Date into Datetime in two ways. Using CONVERT() function: Convert means to change the form or value of something. The CONVERT() function in the SQL server is used to convert a value of one type to another type. Convert() function is used to convert a value of any type to another datatype.
The DATETIMEOFFSET allows you to manipulate any single point in time, which is a datetime value, along with an offset that specifies how much that datetime differs from UTC.
Converting using almost any style will cause the datetime2 value to be converted to UTC.
Also, conversion from datetime2 to datetimeoffset simply sets the offset at +00:00, per the below, so it is a quick way to convert from Datetimeoffset(offset!=0) to Datetimeoffset(+00:00)
declare @createdon datetimeoffset set @createdon = '2008-12-19 17:30:09.1234567 +11:00' select CONVERT(datetime2, @createdon, 1) --Output: 2008-12-19 06:30:09.12 select convert(datetimeoffset,CONVERT(datetime2, @createdon, 1)) --Output: 2008-12-19 06:30:09.1234567 +00:00 I'd use the built in SQL option:
select SWITCHOFFSET(cast('2008-12-19 17:30:09.0000000 +11:00' as datetimeoffset),'+00:00') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
