CODEWITHSUNDEEP
sql-servert-sqldatedatetime
SBB
SBB

Reputation: 8970

Using SQL Server datetimeoffset

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?

Upvotes: 11

Views: 24478

Answers (2)

jhilden
jhilden

Reputation: 12449

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

Upvotes: 4

user2966581
user2966581

Reputation:

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)

Upvotes: 9

Related Questions