2PTTechnology

Declare @DTFROM datetime
set @DTFROM = getdate()

Declare @Apr varchar(5), @Oct varchar(5), @April datetime, @October datetime

--CALCULATE DAYLIGHT SAVINGS TIME------------------------------------------
--FIND THE DAY THAT IT OCCURS ON IN APRIL AND OCTOBER---------------
set @Apr = (2 + 6 * year(@DTFROM) - (floor(year(@DTFROM) / 4))) % 7 + 1
set @Oct = (31 - ((year(@DTFROM) * 5 / 4) + 1) % 7)

set @April = '4/' + @Apr + '/' + convert(char(4),year(@DTFROM))
set @October = '10/' + @Oct + '/' + convert(char(4),year(@DTFROM))
----------------------------------------------------------------------------

select @April, @October

Here is the calculation for 2006 and before and 2007 and beyond

Declare @DTFROM datetime
set @DTFROM = getdate()

Declare @Apr varchar(5), @Oct varchar(5), @Spring_date datetime, @Fall_date datetime, @Mar varchar(5), @Nov varchar(5)

if year(@DTFROM) <=2006
begin
 set @Apr = (2 + 6 * year(@DTFROM) - (floor(year(@DTFROM) / 4))) % 7 + 1
 set @Oct = (31 - ((year(@DTFROM) * 5 / 4) + 1) % 7)
 
 set @Spring_date = '4/' + @Apr + '/' + convert(char(4),year(@DTFROM))
 set @Fall_date = '10/' + @Oct + '/' + convert(char(4),year(@DTFROM))
end
else
begin
 set @Mar = 14 - (floor (1 + year(@DTFROM) * 5 / 4) % 7);
 set @Nov = 7 - (floor (1 + year(@DTFROM) * 5 / 4) % 7);
 
 set @Spring_date = '3/' + @Mar + '/' + convert(char(4),year(@DTFROM))
 set @Fall_date = '11/' + @Nov + '/' + convert(char(4),year(@DTFROM))
end

select @Spring_date as SpringDate, @Fall_date as FallDate

Anonymous:

Here is the calculation for 2006 and before and 2007 and beyond

Declare @DTFROM datetime
set @DTFROM = getdate()

Declare @Apr varchar(5), @Oct varchar(5), @Spring_date datetime, @Fall_date datetime, @Mar varchar(5), @Nov varchar(5)

if year(@DTFROM) <=2006
begin
 set @Apr = (2 + 6 * year(@DTFROM) - (floor(year(@DTFROM) / 4))) % 7 + 1
 set @Oct = (31 - ((year(@DTFROM) * 5 / 4) + 1) % 7)
 
 set @Spring_date = '4/' + @Apr + '/' + convert(char(4),year(@DTFROM))
 set @Fall_date = '10/' + @Oct + '/' + convert(char(4),year(@DTFROM))
end
else
begin
 set @Mar = 14 - (floor (1 + year(@DTFROM) * 5 / 4) % 7);
 set @Nov = 7 - (floor (1 + year(@DTFROM) * 5 / 4) % 7);
 
 set @Spring_date = '3/' + @Mar + '/' + convert(char(4),year(@DTFROM))
 set @Fall_date = '11/' + @Nov + '/' + convert(char(4),year(@DTFROM))
end

select @Spring_date as SpringDate, @Fall_date as FallDate

Hi,

I used your formula and getting desire result. But I want to know the logic of this formula.

Based on what you generate this formula. Please reply me. Without knowing the logic I can't use this formula in our application.

Thanks

Bom

The reason for this formula is to determine when daylight savings occurs.

For example:

Currently I have an application that saves data in Greenwich Mean Time and I’m on US Central time.

So when I run a report to view data for a 24hr period, I have to either add 5 or 6 hours dynamically to the date and time that the user is passing in To accurately get the data for the 24hr period.

So lets say that @DTFROM is the From Date that the user is passing in like in the last example

Once I know if the date that the user is passing in occurred during daylight savings time than I add 5hrs if not than 6hrs

if @ DTFROM > @Spring_date and @ DTFROM < @Fall_date

begin

                set @ DTFROM = dateadd(hour,5,@FDT)

end

else

begin

                set @ DTFROM = dateadd(hour,6,@FDT)

end