Menu
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.
658
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 ...
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.
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() .
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.
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.)
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
