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

I took marc_s answer a step further - here's the code to create a simple timezone table and .net code that generates the inserts for each UTC record:

--TSQL TO CREATE THE TABLE
CREATE TABLE [dbo].[TimeZones] (
[TimeZoneID]           INT              IDENTITY (1, 1) NOT NULL,
[DisplayName]  VARCHAR(100) NOT NULL,
[StandardName]        VARCHAR (100)    NOT NULL,
[HasDST]    BIT  NOT NULL,
[UTCOffset]      INT NOT NULL
CONSTRAINT [PK_TimeZones] PRIMARY KEY CLUSTERED ([TimeZoneID] ASC)
);
GO

To generate the insert statements, I created a default web .net project using visual studio and in the view I pasted this, ran the project and then copied the rendered code (remember to copy it from view source, not from directly the html page):

System.Collections.ObjectModel.ReadOnlyCollection<TimeZoneInfo> timeZones = TimeZoneInfo.GetSystemTimeZones();

foreach (TimeZoneInfo timeZone in timeZones)
{
    Response.Write("INSERT INTO TimeZones (DisplayName, StandardName, HasDST, UTCOffset) VALUES ('" + timeZone.DisplayName.Replace("'", "''") + "', '" + timeZone.StandardName.Replace("'", "''") + "', '" + timeZone.SupportsDaylightSavingTime + "', '" + timeZone.BaseUtcOffset + "')" + Environment.NewLine);
} 

Hope this helps

the list of all timezone are here:

select * from sys.time_zone_info

Enjoy!

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