Using SQL Server datetimeoffset

Question

I am trying to save the date, time and time zone of a meeting that is configured inside the tool. I allow the user to specify the date and time, as well as select from a list of time zones.

My problem is trying to figure out how to take all 3 parts and format them into the correct datetimeoffset for storage.

I believe this is the format that SQL Server requires for datetimeoffset :

 YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] 

My first question is what should be my time zone values?

Secondly, is there a built-in way in SQL Server to pass this data string and convert it to the format needed for datetimeoffset ?

Answer 1

If your tool can convert from time to string, convert it to the format you included. The [.nnnn] is for nanoseconds. I have not seen an application requiring this level of accuracy. If you're okay with second-level accuracy, datetimeoffset(0) enough.

Example:

 DECLARE @time_str varchar(30) = '2015-01-19 7:20:00 -08:00' DECLARE @time datetimeoffset(0) = CONVERT(datetimeoffset, @time_str) SELECT DATEPART(YEAR, @time), DATEPART(MONTH, @time), DATEPART(DAY, @time), DATEPART(HOUR, @time), DATEPART(MINUTE, @time), DATEPART(SECOND, @time), DATEPART(TZOFFSET, @time) 

Answer 2

Yes, DateTimeOffset is exactly what you want.

Secondly, your selection list of available offsets should come from an ISO list ( http://en.wikipedia.org/wiki/List_of_tz_database_time_zones )

SQL Server doesn't care if there is a timezone offset in the real world or not, it just needs to be valid. Here are some examples:

 CREATE TABLE #tmp1 (dto DATETIMEOFFSET); INSERT INTO #tmp1 ( dto ) VALUES ( SYSDATETIMEOFFSET() ) --system timestamp INSERT INTO #tmp1 ( dto ) VALUES ( '2015-01-19 7:20:00 -08:00' ) --valid date, time, and offset INSERT INTO #tmp1 ( dto ) VALUES ( '2015-01-19 7:20:00 -08:16' ) --vaid date, valid time, made up offset that doesn't exist in the real world. SELECT * FROM #tmp1