How To Store Timezone In SQL Server 2005

I am building a web application where users can enter events, including event title start date/time description The users would like to enter the start date/time including a time

Solution 1:

Timezones are tricky, evil things. They're normally stored as a UTC offset, but even that has issues with regards to things like when daylight savings times change over (if at all).

If you're using Sql Server 2008, you can use a datetimeoffset type, which includes the utc offset with the value. Otherwise you'll need two columns.

Solution 2:

Since you are using SQL Server 2005, I would recommend storing the time zone as a string in the database, specifically a 32-character string since that is the limit on length for time zone identifiers in the Windows registry.

The values saved should be the values from the TimeZoneInfo ID property (e.g. "Eastern Standard Time") so that you can do calculations in the .NET Framework more easily.

As Joel said, time zones are evil and tricky. Good luck...

Solution 3:

If you are using SQL Server 2008, you could use datetimeoffset instead of datetime.

Otherwise, I would use tinyint.