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
– Panagiotis Kanavos, Commented Jan 9, 2015 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
– SBB, Commented Jan 9, 2015 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
– Panagiotis Kanavos, Commented Jan 9, 2015 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
– SBB, Commented Jan 9, 2015 at 15:18

user2966581 · Accepted Answer · 2015-01-19 15:25:51Z

9

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)

answered Jan 19, 2015 at 15:25

user2966581

Sign up to request clarification or add additional context in comments.

Comments

jhilden · Accepted Answer · 2015-01-19 16:03:35Z

4

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

answered Jan 19, 2015 at 16:03

jhilden

12.5k5 gold badges56 silver badges77 bronze badges

Collectives™ on Stack Overflow

Using SQL Server datetimeoffset

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related