Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What should I map a TimeZoneInfo property to a SQL Server 2008 DB Type?

I have a TimeZoneInfo property on some object which I need to save to a Microsoft SQL Server 2008 database. What type of database field type should I use, please?

If it helps, I'm also using Entity Framework 4, which came with my Visual Studio 2010.

like image 658
Pure.Krome Avatar asked Oct 25 '10 05:10

Pure.Krome


People also ask

How does SQL Server handle time zones?

I might be missing something obvious, but if all you want to do is display the date using the user timezone and language format, the simplest way is to always store the dates in UTC in the database and let the client application convert from UTC to local timezone and use the user regional settings to format the date ...

What is data type for time in SQL?

SQL Server outputs date, time and datetime values in the following formats: yyyy-mm-dd, hh:m:ss. nnnnnnn (n is dependent on the column definition) and yyyy-mm-dd hh:mm:ss.

Does SQL date store timezone?

SQL Server does not store time zone data when storing timestamps. It uses the host server time as the basis for generating the output of getdate() .

How can use time datatype in SQL Server?

hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.


1 Answers

You should use the ID of the TimeZoneInfo - as then you can retrieve the original zone again with TimeZoneInfo.FindSystemTimeZoneById.

Note that while storing dates and times as a DateTimeOffset is a valid alternative in some situations, it isn't sufficient for all. For example, suppose you want to store the information that you have a 3pm meeting every week. Storing a single instance of that as a DateTimeOffset won't tell you when the meeting is next week - because you won't know whether daylight saving time has changed. (In that situation you'd probably want to store the local time of day, the fact that it's a weekly meeting, and the day of the week. Recurrence rules get complicated fast, unfortunately.)

like image 186
Jon Skeet Avatar answered Nov 15 '22 07:11

Jon Skeet