September 9, 2005 at 4:27 pm
Anyone have a recommendation or sample code on how to determine if the system date is Standard or Daylight Saving time? Assuming location is in a time zone that utilizes daylight savings. I have failed thus far to work the date functions into returning the correct value. One recommendation I received was to use a static lookup table. I would prefer to do this entirely in the code if possible.
Daylight Savings begins at
Thanks very much in advance.
GerryV
September 10, 2005 at 12:26 am
Try these links to create time span in SQL Server and you should get the expected result. Hope this helps.
http://www.stanford.edu/~bsuter/sql-datecomputations.html
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
September 10, 2005 at 11:44 pm
Didn't the Feds just modify the dates for DST?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2005 at 8:03 pm
Not sure about the dates moving but I will confirm before completing this task.
Thanks for the links Gift. The main riddle to solve is being able to find the date that each Sunday or weekday fales on. I almost have it figured out using datepart weekday function. I believe one way to solve it is to find out what the date and weekday is for the first day of the month. This information should provide enough to calculate each date for each Sunday of that month. I will post it once I get it working.
Thanks again,
GerryV
September 12, 2005 at 1:04 am
Here's something I had lying around in my archive. Never tried it though, but it may be of some help I hope.
-----Original Message-----
From: Sydney_Lee@discovery.com <Sydney_Lee@discovery.com>
To: SQL 7 Discussions <sql7@ls.swynk.com>
Date: Thursday, September 09, 1999 4:13 PM
Subject: Re: Default GMT for server.
>
>
>How about something like this for US daylight savings time? BTW, the rules
are
>different in some European countries and Asia, check with one of the many
sites
>out there that deal with Daylight Savings Time for the various locality's
rules
>
>/* New Code added for programmatically finding the given year's DST times
>Calculates daylight Savings time for a given year
>The DST RULE IS:
>DST begins on the first Sunday in April
>DST ends on the last Sunday in October
>Day of week 1 (Sunday) - 7 (Saturday) */
>
>declare @dw tinyint,
> @dst_Apr datetime,
> @dst_Oct datetime,
> @year int
>/* Get the year for the year ranges
>Then create the datetimes for the min and max range of
>each dst function.. Hard coding is permitted as
>these will ALWAYS be the same unless Congress changes it. */
>
>select @year = Datepart(year, GetDate())
>-- April and October DST ranges for the 'Year'
>select @dst_Apr = convert(datetime,'04/01/'+convert(char(4),@year)),
> @dst_Oct = convert(datetime,'10/31/'+convert(char(4),@year))
>-- Retrieve Day of Week for the Fall back date
>select @dw=datepart(dw,@dst_Oct)
>-- If the day of Week = 1, then the date is Sunday
>-- Otherwise, calculate the last Sunday for the Spring-Back Sunday.
>
>if @dw !=1
>select @dst_Oct=dateadd(dd,-(@dw-1),@dst_Oct)
>
>-- Day of week 1 (Sunday) - 7 (Saturday)
>select @dw=datepart(dw,@dst_Apr)
>
>-- Calculate the First Sunday for the Spring-Forward Sunday.
>
>if @dw!=1
>select @dst_Apr=dateadd(dd,8-@dw,@dst_Apr)
>
>
>Sydney
>
>
/Kenneth
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply