Menu
In SQL Server I need to find the offset of a given datetimeoffset(7).
I have researched the documentation and there are all ways to change the offset but not a way to know the offset of a particular value (sorry if I missed it).
Well I came up with the following piece of code that I find too complicated although seems to work.
DECLARE @datetimeOffset datetimeoffset(7) SET @datetimeOffset = CAST('2007-05-08 22:35:29.1234567-05:00' AS datetimeoffset(7)) DECLARE @result datetimeoffset(7) DECLARE @offsetMin int SET @offsetMin = DATEDIFF(mi, cast(SWITCHOFFSET(@datetimeOffset, '+00:00') as datetime), cast(@datetimeOffset as datetime)) SELECT @offsetMin I still have to do the conversion to the +00:00 format but wanted to check if there is a better way of doing this.
Thanks!
855
Extracting the Time Zone Offset You can use the DATEPART() function to return the time zone offset. This function returns an integer that represents the time zone offset in minutes. You can also use the FORMAT() function to return the time zone offset as a string.
SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds.
The SQL Server DateTimeOffset data type stores the date & time along with the Time Zone Offset. It is similar to both DateTime & DateTime2 data types. Except that the DateTime & DateTime2 does not store the Time Zone Offset.
The datepart function has a tz option which is the timezone offset in minutes.
datepart(tz,@datetimeoffset) 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
