 |
|
|
Great solution, but I just want to restate a few things people have mentioned in the comments:
US Daylight saving time rules changed in 2007. The default data must be updated to reflect this change. A good resource for latest US rules is http://www.webexhibits.org/daylightsaving/b2.html[^]
The DST rules in this solution represent a static view of the DST rules. If you are converting historic dates, you must use care to make sure the DST rules you use are accurate for the dates your are converting. Since US DST rules changed in 2007, a single rule cannot be used to convert 2006 and 2008 dates.
|
|
|
|
|
|
|
Yes - this has caused me some grief as well.
Basically I modified the table and added a StartDate and EndDate field. This allows me to utilize multiple rules for each timezone (PK = TimeZoneID & StartDate).
I then just adjusted the following code to the queries to return the TimeZone Data.
FROM tbTimeZoneInfo
WHERE TimeZoneID = @TimeZoneID
AND StartDate <= @LocalDateTime
AND @LocalDateTime < EndDate
|
|
|
|
|
|
|
very helpful.
I saved my time.
|
|
|
|
|
|
|
Thanks for the article. It helped me lot. Simple solution that works!!
Kishor
|
|
|
|
|
|
|
I could be completely out of my mind, but based on the research I have done (using this code as a structure \ sample to write my own functions against a similar timezone table that my application has), the function which retreives the Daylight and Standard begin dates will not work correctly for Southern Hemisprere timezones.
For example, Brasilia starts Daylight Saving in Oct 2010 but Standard time begins in Feb. 2011 - the function assumes the same year to calculate the dates.
Any thoughts on this?
|
|
|
|
|
|
|
Does not seem to work for Australia ...
|
|
|
|
|
|
|
Just modified the GetLocalDateTime with the following and it seems to work for everything correctly.
IF ( @StandardDate > @DaylightDate )
BEGIN
IF ( DATEADD( minute, @Bias, @UTCDate ) BETWEEN @DaylightDate AND @StandardDate )
SET @DltBiasFactor = 0
ELSE
SET @DltBiasFactor = 1
END
ELSE
BEGIN
IF ( DATEADD( minute, @Bias, @UTCDate ) BETWEEN @StandardDate AND @DaylightDate )
SET @DltBiasFactor = 0
ELSE
SET @DltBiasFactor = 1
END
|
|
|
|
|
|
|
|
|
|
I think that we can fix the data involved in this to reflect the 2007-on U.S. daylight savings schedule in the following way:
In the UTCToLocalData.sql file, do a find and replace:
Find:
10,0,5,2,4,0,1,2
Replace with:
11,0,1,2,3,0,2,2
where that string means
DST starts 11 = November, 0 = Sunday, 1 = 1st week, 2 = 2am,
DST ends 3 = March, 0 = Sunday, 2 = 2nd week, 2 = 2am
|
|
|
|
|
|
|
What is a solution if my data spans 2007?
-Andy
|
|
|
|
|
|
|
The GetDaylightStandardDateTime function has a small issue where if the SQL Server's DateFirst value is not set to 7 (Sunday), the date gets calculated incorrectly. Normally to make accomodation for such a scenario you can just call
set datefirst 7
However, since the code resides in a function and you cannot call the SET command within functions, you can resolve this by changing the following line of code:
SET @DoW = DATEPART( weekday, @FirstOfMonth )
to:
SET @DoW = DATEPART( weekday, @FirstOfMonth ) - (7 - @@DATEFIRST)
Tim Friesen
|
|
|
|
|
|
|
great script, highly recommended... easy to edit time zones if necessary... thanks very much!
|
|
|
|
|
|
|
|
|
|
Hi,
when i download and un-zip it, its size is zero bye and i got "archive file is damager or unknown format " error.
|
|
|
|
|
|
|
Thanks for this solution...works great. I was very frustrated before finding this solution.
For others who want to use this, note that the daylight savings time info for N. America is pre-2007. To correct it, , in UTCToLocal_Data.SQL, change a typical INSERT string from:
...'(GMT-08:00) Pacific Time (US & Canada); Tijuana',480,0,-60,10,0,5,2,4,0,1,2)
to
...'(GMT-08:00) Pacific Time (US & Canada); Tijuana',480,0,-60,11,0,1,2,3,0,2,2)
I created another function, identical to dbo.GetLocalDateTime, called it dbo.GetLocalDateTimeObj, that returns a DATETIME object instead of a string.
I changed two lines in dbo.GetLocalDateTime:
RETURNS NVARCHAR(500)
to
RETURNS DATETIME
and
RETURN 'Time Zone ID:' + CAST( @TimeZoneID AS CHAR(2) ) ...
to
RETURN @LocalDateTime
I'm using this in PHP queries to retrieve the Local time from UTC date stamps stored in the database, like so:
SELECT dbo.GetLocalDateTimeObj(UTCTimeStamp, 13) AS LocalTimeStamp FROM MyTable...
In the code snippet above, UTCTimeStamp is a column of type DATETIME in the table MyTable, and LocalTimeStamp returns a DATETIME object from the query. The "13" in the code snippet is my timezone, EST, but in the shipping version of the software, this will be a variable, of course.
|
|
|
|
|
|
|
I understand how most of the table is populated, however I am confused as to where the StdWeek and DltWeek columns come from. I look at the TIME_ZONE_INFORMATION and SYSTEMTIME structures, and I do not see a reference to the week number.
I am interested in this solution. I would like, however, to fully understanding of how this table is populated so that I can easily populate it when changes are necessary.
Help?
Thanks!
|
|
|
|
|
|
|
I'm not familiar with the TIME_ZONE_INFORMATION and SYSTEMTIME structures, however, the government rules for changing from daylight savings time to standard time, right now, read "at 2:00am on the FIRST Sunday of November...", so StdWeek is 1, i.e., the FIRST week. Similarly, change to DST is at 2:00 am on the SECOND Sunday of March, so DltWeek is 2.
|
|
|
|
|
|
|
I am also interested in knowing how the "week" values were derived. I am trying to write a small util app that re-populates this table periodically. The Windows registry does not seem to store these values.
Tim Friesen
|
|
|
|
|
|
|
I found it!
It looks like the Day value of the SYSTEMTIME struct actually contains the week number.
According to MSDN (http://msdn.microsoft.com/en-us/library/ms725481(VS.85).aspx[^])
StandardDate
A SYSTEMTIME structure that contains a date and local time when the transition from daylight saving time to standard time occurs on this operating system. If the time zone does not support daylight saving time or if the caller needs to disable daylight saving time, the wMonth member in the SYSTEMTIME structure must be zero. If this date is specified, the DaylightDate member of this structure must also be specified. Otherwise, the system assumes the time zone data is invalid and no changes will be applied.
To select the correct day in the month, set the wYear member to zero, the wHour and wMinute members to the transition time, the wDayOfWeek member to the appropriate weekday, and the wDay member to indicate the occurrence of the day of the week within the month (1 to 5, where 5 indicates the final occurrence during the month if that day of the week does not occur 5 times).
Using this notation, specify 02:00 on the first Sunday in April as follows: wHour = 2, wMonth = 4, wDayOfWeek = 0, wDay = 1. Specify 02:00 on the last Thursday in October as follows: wHour = 2, wMonth = 10, wDayOfWeek = 4, wDay = 5.
If the wYear member is not zero, the transition date is absolute; it will only occur one time. Otherwise, it is a relative date that occurs yearly.
Tim Friesen
|
|
|
|
|
|
|
To convert back from local to UTC you may create another function (GetUtcDateTime?), put the same code that is in GetLocalDateTime and make the following changes:
Replace:
SET @LocalDateTime = DateAdd( minute, @Bias , @UTCDate)
with
SET @LocalDateTime = DateAdd( minute, -@Bias , @UTCDate)
and
SET @LocalDateTime = DATEADD( minute, @Bias + ( @DltBiasFactor * @DltBias ) , @UTCDate )
with
SET @LocalDateTime = DATEADD( minute, -@Bias - ( @DltBiasFactor * @DltBias ) , @UTCDate )
Renaming variable names would be a good idea as well 
|
|
|
|
|
|
|
This line works not correctly in SQL Server 2000 in Germany:
SET @FirstOfMonth = CAST( @Year AS NVARCHAR ) + '/' + CAST( @Month AS NVARCHAR ) + '/01'
Try:
DECLARE @FirstOfMonth datetime
SELECT @FirstOfMonth=CAST( 2007 AS NVARCHAR ) + '/' + CAST( 10 AS NVARCHAR ) + '/01'
SELECT @FirstOfMonth
returns "2007-01-10 00:00:00.000" and not "2007-10-01 00:00:00.000"!
Change the critical line to
IF @Month>9 BEGIN
SET @FirstOfMonth = CAST( @Year AS NVARCHAR ) + '-' + CAST( @Month AS NVARCHAR ) + '-01T00:00:00'
END ELSE BEGIN
SET @FirstOfMonth = CAST( @Year AS NVARCHAR ) + '-0' + CAST( @Month AS NVARCHAR ) + '-01T00:00:00'
END
|
|
|
|
|
|
|
Hi,
I am ravi .
I am using this great sql function.
Actually I want to update the table with resent data in registry.
Will U please give me some Idea , how i should proceed for that
And one more thing Local time is comming one hour less when I convert Indian ust time to some other loca time zone
Thanks
Ravi Kumar Mishra
ravikrmishra@gmail.com
|
|
|
|
|
|
|
Hi, I believe there is a small problem with the GetLocalDateTime() function.
On line 161 of UTCToLocal_Objects.sql, the following section of code:
IF ( DATEADD( minute, @Bias, @UTCDate ) BETWEEN @StandardDate AND @DaylightDate )
BEGIN
SET @DltBiasFactor = 0
END
should be changed to:
IF NOT ( DATEADD( minute, @Bias, @UTCDate ) BETWEEN @StandardDate AND @DaylightDate )
BEGIN
SET @DltBiasFactor = 1
END
|
|
|
|
|
|
|
Hello,
Thanks for the response. Please make sure to test thoroughly before using in any critical application.
The way you can return just the local date is as follows:
In the dbo.GetLocalDateTime_TEST function simply make 2 changes:
Change 1 Line 11
CURRENT - RETURNS NVARCHAR(500)
CHANGE TO - RETURNS DATETIME
Change 2 Line 83 ( at the end of the function )
CURRENT - RETURN 'Time Zone ID:' + CAST( @TimeZoneID AS CHAR(2) ) + ' - ' + @Display + ' - - '
CHANGE TO - RETURN @LocalDateTime
Actually I would simply create a new function called "dbo.GetLocalDateTime" with these changes and leave the old one for testing when you make changes to the tbTimeZoneInfo table.
Let me know if this works for you. I can send you a script if you like but it might be a good exercise to create this new function yourself with the changes described above.
|
|
|
|
|
|
|
This is absolutley great. As a sql newbie I had no idea how to get around this issue. Thanks for sharing this. One question, If I simply wanted to to return the local date and time without the UTC pretext, would a trim function be the best way?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin