Using SQL Server datetimeoffset

Question

I am trying to store the date, time and timezone of a meeting that is set up within a tool. I allow the user to specify the date and time as well as select from a timezone list.

My issue is trying to figure out how to take all 3 parts and format it into a correct datetimeoffset to store.

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

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

My first question with this is what should the values of my timezone drop down be?

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

Dates have **no** format. They are binary values. Formats apply only when converting/parsing strings. Use parameterized queries and datetimeoffset-typed parameters and you won't have to do with parsing at all — Panagiotis Kanavos, Jan 09 '15 at 14:48
Awesome, so now I just need to figure out how to generate a list of timezones with their offset value; thanks! — SBB, Jan 09 '15 at 14:56
@SBB DateTimeOffset only includes the offset, not the timezone. Everyone except Windows (the OS) uses the `tzData` database. Check Jon Skeet's NodaTime library which handles both timezones, offsets and includes tzdata — Panagiotis Kanavos, Jan 09 '15 at 15:17
That is what I meant, I will present a list of timezones to the user but the value will be the offset which will be used in combination of date & time to provide the `datetimeoffset`, correct? — SBB, Jan 09 '15 at 15:18
Yes, check this [question](http://stackoverflow.com/questions/13056759/how-should-i-populate-a-list-of-iana-olson-time-zones-from-noda-time) on how to extract the info from NodaTime. — Panagiotis Kanavos, Jan 09 '15 at 15:22

score 9 · Accepted Answer · answered Jan 19 '15 at 15:25

If your tool can convert from time to string, convert it to the format you included. The [.nnnn] part is for nanoseconds. I haven't seen an application needing that level of accuracy. If you are OK with second-level accuracy, datetimeoffset(0) is 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)

score 4 · Answer 2 · answered Jan 19 '15 at 16:03

Yes, DateTimeOffset is exactly what you want.

Secondly, your select list for available offsets should come from the ISO list (http://en.wikipedia.org/wiki/List_of_tz_database_time_zones)

SQL Server doesn't care if the timezone offset exists in the real world or not, it just needs to be valid. Here are a couple samples:

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

Using SQL Server datetimeoffset

2 Answers2