Daylight Savings Handling in DateDiff() in MS Access?
- by PowerUser
I am fully aware of DateDiff()'s inability to handle daylight savings issues. Since I often use it to compare the number of hours or days between 2 datetimes several months apart, I need to write up a solution to handle DST. This is what I came up with, a function that first subtracts 60 minutes from a datetime value if it falls within the date ranges specified in a local table (LU_DST). Thus, the usage would be:
datediff("n",Conv_DST_to_Local([date1]),Conv_DST_to_Local([date2]))
My question is: Is there a better way to handle this? I'm going to make a wild guess that I'm not the first person with this question. This seems like the kind of thing that should have been added to one of the core reference libraries. Is there a way for me to access my system clock to ask it if DST was in effect at a certain date & time?
Function Conv_DST_to_Local(X As Date) As Date
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("LU_DST")
Conv_DST_to_Local = X
While rst.EOF = False
If X > rst.Fields(0) And X < rst.Fields(1) Then Conv_DST_to_Local = DateAdd("n", -60, X)
rst.MoveNext
Wend
End Function
Notes
I have visited and imported the BAS file of http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx. I spent at least an hour by now reading through it and, while it may do its job well, I can't figure out how to modify it to my needs. But if you have an answer using his data structures, I'll take a look.
Timezones are not an issue since this is all local time.
