Can you check whether this will work for you ?
-------------------------------------------------------------
create table #timetable
(starttime datetime, endtime datetime, diffhours tinyint)
insert into #timetable values ('2001/04/01 02:00', '2001/10/28 02:00', 7)
insert into #timetable values ('2001/10/28 02:00', '2002/04/07 02:00', 6)
insert into #timetable values ('2002/04/07 02:00', '2002/10/27 02:00', 7)
insert into #timetable values ('2002/10/27 02:00', '2003/04/06 02:00', 6)
delete from #timetable
select * from #timetable
create table #timeconvert
(date1 datetime)
insert into #timeconvert values ('2001/09/01 23:00')
insert into #timeconvert values ('2001/10/29 23:00')
insert into #timeconvert values ('2002/03/01 23:00')
insert into #timeconvert values ('2001/04/25 23:00')
select * from #timeconvert
Select Dateadd(hh, -diffhours,date1) as 'CST' from #timeconvert, #timetable
where date1 >= starttime and date1 < endtime
----------------------------------------------------------------------
John
www.ideo.com
-----Original Message-----
From: Patrick Gallucci [mailto:[Email Address Removed] January 31, 2003 1:13 PM
To: LazyDBA.com Discussion
Subject: RE: A riddle...
Right...Can do with a timedimtable...just trying to see if someone has
roled the logic in a udf or just query...1 statement..
We have timedim table but for millions of records and multiple date/time
columns per table, the lookup performace would just kill us...Below is
an explanation and example...
The problem is that all transactions are stored in GMT..2 times a year
we either lose or gain an hour in CST (first Sunday in april 2:00am
becomes 1:00am then back in october)....so see if this is clear..
The offset changes from -6 hours to -5 hours
(http://greenwichmeantime.com/local/home.htm) in april and then back in
october...
Below is a trimmed down example of what we are doing...
set nocount on
go
use pubs
go
if exists (select * from pubs..sysobjects where name = 'timezonetest')
drop table timezonetest
GO
create table timezonetest
(id int,
gmt smalldatetime)
GO
truncate table timezonetest
go
declare @recVar int
declare @hourVar int
declare @dayVar int
declare @monthVar int
declare @startdateVar smalldatetime
set @monthVar = 0
set @dayVar = 0
set @hourVar = 0
set @recVar = 0
set @startdateVar = '01/01/2002'
--12 months
while (@monthVar < 13)
begin
--31 days handle errors
while (@dayVar < 32)
begin
--24 hours
while (@hourVar < 25)
begin
insert into timezonetest (id, gmt)
values (@recVar, dateadd(hh, @hourVar,
dateadd(d, @dayVar, dateadd(m, @monthVar, @startdateVar))))
set @recVar = @recVar + 1
set @hourVar = @hourVar + 1
end
set @hourVar = 0
set @dayVar = @dayVar +1
end
set @monthVar = @monthVar + 1
set @dayVar = 0
end
--example of what i need...except it has to be flexible enough to
account for DST
--select gmt as [greenwich mean time], [conversion routine] as [central
standard time] from timezonetest
select gmt as [greenwich mean time], dateadd(hh, -6, gmt) as [central
standard time] from timezonetest order by gmt
results from above are:
greenwich mean time,central standard time
2002-04-07 05:00:00,2002-04-06 23:00:00
2002-04-07 06:00:00,2002-04-07 00:00:00
2002-04-07 07:00:00,2002-04-07 01:00:00
2002-04-07 08:00:00,2002-04-07 02:00:00
2002-04-07 09:00:00,2002-04-07 03:00:00
2002-04-07 10:00:00,2002-04-07 04:00:00
what i expect is:
greenwich mean time,central standard time
2002-04-07 05:00:00,2002-04-06 23:00:00
2002-04-07 06:00:00,2002-04-07 00:00:00
2002-04-07 07:00:00,2002-04-07 01:00:00
2002-04-07 08:00:00,2002-04-07 01:00:00
2002-04-07 09:00:00,2002-04-07 02:00:00
2002-04-07 10:00:00,2002-04-07 03:00:00
-----Original Message-----
From: Morrison, Joshua: [mailto:Joshua.[Email Address Removed]
Sent: Friday, January 31, 2003 12:26 PM
To: '[Email Address Removed] RE: A riddle...
minus 6 hours for all data. CST is always CST independent of Daylight
Savings time.
Joshua Morrison MCSE/MCDBA
ConocoPhillips IT SQL DBA
602-728-4629
602-402-6157 Cell
> -----Original Message-----
> From: [Email Address Removed] [SMTP:[Email Address Removed] Sent: Friday, January 31, 2003 11:13 AM
> To: LazyDBA.com Discussion
> Subject: A riddle...
>
> All my dates are stored in gmt in the database...
>
> I now need to convert all the dates to cst. How do I do this for say
> 20 years of data and also take in to account daylight savings time
> from the past years?
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
> commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page