Geek Answers Handbook

Where can I get a simple timezone table to use on a SQL server?

I just need to create an SQL timezone table - for now, I just need to fill out the drop-down list in the online form. I present something simple with columns for the ISO code, name, UTC offset, and possibly a list of representative cities. I thought I could easily find something online to copy and paste, but could not find anything.

I directed this question to the tz database , which is binary and seems redundant for what I need. Alternatively, I could combine this with sites like TimeAndDate.com , but this seems like more work than necessary.

Or am I mistaken about this - for example, should I get this information from the server OS?

+5
source share
5 answers

Are you using .NET 3.5? You can easily get a list of time zones in .NET 3.5, and then save this information (or at least everything you need) in your SQL Server database.

You can iterate over all available and known .NET 3.5 times and save the relevant information in 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
}

Mark

+12
source

I took marc_s one step further - here is the code for creating a simple time zone table and .net code that generates inserts for each entry in UTC format:

--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

, webnet visual studio , , , ( , html):

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);
}

,

+6

:

select * from sys.time_zone_info

!

+2

.

, asp.net, , .

+1

More articles:


All Articles