Where can I get a simple table of time zones for use in SQL server?

Question

I just need to make a SQL table of time zones - at this point, my only need is to populate a drop-down list on an online form. I'm envisioning something simple with columns for ISO code, name, UTC offset, and perhaps a list of representative cities. I thought I would be able to easily find something online to copy-and-paste, but haven't been able to locate anything.

I was directed by this question to the tz database, which is in binary form and seems like overkill for what I need. Alternatively I could piece this together from sites like TimeAndDate.com, but that seems like more work than should be necessary.

Or am I going about this the wrong way - e.g. should I be getting this information from the server's OS?

Answer 1

I you want a good list to copy/paste from : http://en.wikipedia.org/wiki/Timezones

Answer 2

Are you on .NET 3.5 ? You can easily get a list of timezones in .NET 3.5 and then store that information (or at least whatever you need of it) in your SQL Server database.

You could iterate over all timezones available and known to .NET 3.5 and store the relevant info to a SQL Server table:

ReadOnlyCollection<TimeZoneInfo> timeZones = TimeZoneInfo.GetSystemTimeZones();

foreach (TimeZoneInfo timeZone in timeZones)
{
    // store whatever you need to store to a SQL Server table
}

Marc

Answer 3

Get it from the OS.

In that you've tagged this asp.net have a look at this example of how to enumerate timezones.