11-09-06, 12:52 #1Registered User
- Join Date
- Jun 2005
Unanswered: Time Zone challenge - All ideas welcome
I have been tasked with an interesting challenge and was wondering if any of you experts out there might have any ideas on a solution. Basically, I have a table of packages:
CREATE TABLE [dbo].[tPackage] ( [PkgID] [int] NOT NULL PRIMARY KEY CLUSTERED , [PkgDelivZip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PkgDelivDate] [smalldatetime] NULL , [HasBeenDeliv] [bit] NULL ) ON [PRIMARY] GO INSERT INTO tPackage VALUES (1, '30022', '11/9/06 10:30 am', 1) INSERT INTO tPackage VALUES (2, '30022', '11/9/06 10:30 am', 0) INSERT INTO tPackage VALUES (3, '30022', '11/9/06 06:00 pm', 0) INSERT INTO tPackage VALUES (4, '96801', '11/9/06 10:30 am', 1) INSERT INTO tPackage VALUES (5, '96801', '11/9/06 10:30 am', 0) INSERT INTO tPackage VALUES (6, '96801', '11/9/06 06:00 pm', 0)
SELECT * FROM tPackage WHERE HasBeenDeliv = 0 AND PkgDelivDate < GetDate()
Unfortunately, in the case of package 5, it is not after 10:30am in Hawaii, so this should not be considered late. I thought about having a table of zipcodes with their timezone differential, but the really big problem comes in when you consider the areas which do not observe Daylight Savings Time.
Thanks in advance for your feedback!
11-09-06, 13:14 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Daggone it! If people would just do the sensible thing and store all times in UCT these problems wouldn't happen to us poor DBAs! That's about as likely to happen as pigs learning to fly, but we can always hope...
The folks at Great Data can help you. They mantain a list of zip codes, and one of the options you can purchase is time zone information. They use standard time zones, which have specific zone identifiers for areas that don't observe Daylight Savings Time.
You're "on your own" for dealing with when the time changes from Standard to Daylight Savings time, I don't know of any way to cope with that, short of actually storing the times in the database as UCT and converting them for the UI.
11-09-06, 13:17 #3Registered User
Provided Answers: 11
- Join Date
- Jan 2003
Perhaps you should use UTC dates (which I believe do not play around with Daylight Savings schemes)? That way, you could use the getutcdate() function to determine if a package is late.
EDIT Dang, I got sniped!