Q: I have a table that imports data that has a date and time stamp in Arizona. The time needs to be converted into the Central Time Zone. So, for half of the year, I need to add 2 hours and the other half of the year I need to add one hour. Is there a way to create a query where it knows how many hours to add based on daylight savings? I'd prefer the query to be in VBA. - Art
A: Good question.
Well, you'd need to know what dates you're working with for your calculations. A quick Google search tells me that DST goes into effect in 2008 on March 9, and ends on November 2. Knowing this, we can just create a CALCUALTED QUERY with the IIF function to add the hours when appropriate.
Assuming only dates in 2008, and that the date stored in your table is called MyDateTime, then our new field would be:
NewDateTime: IIF(MyDateTime >= #3/9/2008# AND MyDateTime <= #11/2/2008#, MyDateTime + (1/24), MyDateTime)
In English, this basically says if MyDateTime is between 3/9/08 and 11/2/08 then set NewDateTime equal to MyDateTime plus one hour (1/24 of a day), otherwise, leave it alone.
Now if you need to adjust for time zones, add another hour where appropriate.
Also, this of course only takes into consideration calculations FROM ONE YEAR (2008). If you need to calculate different years, you'll need to get a list of all the DST dates. I would probably load them all into a table and then perform DLOOKUPS to determine the start and end dates for each year.
Now you're getting complicated, and that's WAY more time than I could take to answer your question here. Perhaps it would make a good tutorial, though.
18 years ago
