Changing time difference automatically for moment in time report 1

[Shawn12]

I am hoping someone might have a solution or idea that might help me save alot of time. I have 2 databases that we combine data from and one is in the eastern time zone while the other is in Phoenix. The proble is that Phoenix does not change time twice a year like the rest of us. Currently I have to manually change queries to adjust for this time difference so our reporting across sites is "moment in time" by adding either 2 or 3 hours to the timestamp depending on the current time status. Is there a was in SQL that I could have the query say if its daylight saving time add 3 hours and if its standard time add 2 hours? And if so would there be a way for me to query my data for say >=DATE()-1 and add 2 hours to that as well? I tried Date()-1.125 and that does not work. Any help would be greatly appreciated.

 

[SkipVought]

hi,

>=DATE()-1 + timeserial(2,0,0)  'and add 2 hours to that as well

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Shawn12]

Thanks much this helps with getting the data I need from the previous day...now if I can just getthe query to know whether we are in daylight savings or standard I am all set.

 

[dhookom]

I am a strong advocate of storing data in tables. You could create a table of date ranges with the hours to add to the field.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

As Duane says you can write a function to do this or store the data. I would concur that storing the data is much more efficient especially in a big table. Your data table then is something like:

timePeriod startPeriod           endPeriod             hoursToAdd
DST        3/14/2010 2:00:00 AM  11/7/2010 1:59:59 AM  0.125
DST        3/13/2011 2:00:00 AM  11/6/2011 1:59:59 AM  0.125
ST         11/7/2010 2:00:00 AM  3/13/2011 1:59:59 AM  0.083333
ST         11/6/2011 2:00:00 AM  3/11/2012 1:59:59 AM  0.083333

then your query is something like

SELECT 
 tblTimes.ArizonaTime, 
 (select hoursToAdd from tblEasternConversion where tblTimes.arizonatime >= StartPeriod and arizonaTime <= endPeriod) AS hoursToAdd, 
 [ArizonaTime]+[hoursToAdd] AS EasternTime
FROM tblTimes;

 

[dhookom]

I feel like part of a 3 person tag-team ;-)


Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Phoenix does not change time twice a year like the rest of us

Could you just have another timestamp column in your table that always 'points' to Phoenix's time (since it never changes)? Then you don't have to do anything (except to code time changes from summer to winter and back at your East location).

Have fun.

---- Andy

 

[Shawn12]

Thanks everyone I will give the table route a shot.