Automatically adjust for DST time change

[Phailak]

Hail,

Times in our application's DB is kept in GMT time so that when I query the DB I have to specify with dateadd the amount of hours to adjust to EST. Now this is either -4 from April to october or -5 from october to april. As it stands, I have to go into every query that has reports everytime and manually change this. Is there a function I can use to determine when it's -4 or -5.

Phailak

 

[vb6novice]

Phailak,

There are several possibilities.

The easiest, although not exactly accurate, way is to change to the part of the query to where you subratct 4 or 5 to determine whether it is 4 or 5 based on the current month.

For example, if your query currently has a field that is similar to FieldName: GMT-5, change that to

FieldName: =Iif(Month(date())>3 And Month(Date())<11,[GMT]-4,[GMT]-5)

That is inaccurate because the time change does not occur on the last day of the month.

Perhaps a more accurate but still simple way would be to have a table with a single field and a single record indicating whether or not the US was in DST or not. You query could do a lookup on that field (make it a Yes/No) field. If the US is in DST, than subtract 4, if not, then subtract 5. For example, name the table tblDST and name the field DST. Then your query statement could be
FieldName: =Iif(Dlookup(&quot;[DST],&quot;tblDST&quot = 0,[GMT]-5,[GMT]-4)

Then all you have to do is change the value of DST when it changes and all the queries that you have the statement in will return the right time adjustment.

The most complicated, but most accurate, would be to have a table listing the years and dates in those years on which DST goes on and off and have the system determine whether the current date is between those dates. If it is, subtract 4, if not subtract 5.

 

[Phailak]

Hail,

Thanks for the suggestion. Actually, what I'm using now is a module with VB code simply doing a loop to retrieve the last sunday of october and 1st sunday of april. Then I use those in a < or > and determine a variable. I set the SQL statement. This is ran everytime before the report. It works well but it's not very neat, I was hoping for a quick fix with some functions I had never used. Thanks anyway...

Phailak

 

[vb6novice]

Phailak,

Didn't know you already had a code solution. It sounds like what you're doing is the best way to go. It's probably the fastest and (if the time change is always first Sunday in April and last Sunday in October), most accurate.

 

[Phailak]

Hail,

Yep, here it is if you're interested...

Dim Year, AprilDay, AprilDate, OctoberDate, OctoberDay, April, October
'Find current year
Year = Format(Date, &quot;yyyy&quot
'Add day and month for April to determine first day to check for sunday
AprilDate = Format(Year & &quot;-04-01&quot;, &quot;yyyy-mm-dd&quot
'Add day and month for October to determine first date to check for sunday
OctoberDate = Format(Year & &quot;-10-31&quot;, &quot;yyyy-mm-dd&quot
'Find current date for April's change of hour (Back to -4 hours)
Do Until April <> &quot;&quot;
AprilDay = Format(AprilDate, &quot;ddd&quot
If AprilDay = &quot;dim&quot; Or AprilDay = &quot;Sun&quot; Then 'Language issue in Montreal!!! If sunday, we've got our date
April = Format(AprilDate, &quot;yyyy-mm-dd&quot
Else 'If not a sunday, add a day until you find it
AprilDate = DateAdd(&quot;d&quot;, 1, AprilDate)
End If
Loop
'Find current date for October's change of hour (Back to -5 hours)
Do Until October <> &quot;&quot;
OctoberDay = Format(OctoberDate, &quot;ddd&quot
If OctoberDay = &quot;dim&quot; Or OctoberDay = &quot;Sun&quot; Then
October = Format(OctoberDate, &quot;yyyy-mm-dd&quot
Else 'Same as before except we're going backwards here...
OctoberDate = DateAdd(&quot;d&quot;, -1, OctoberDate)
End If
Loop
Dim DST, DST1stTime, DST2ndTime
'Determine DST -4 or -5 hours
If Format(Date, &quot;yyyy-mm-dd&quot <= Format(April, &quot;yyyy-mm-dd&quot Then
DST = &quot;-5&quot;
DST1stTime = &quot;04:59&quot;
DST2ndTime = &quot;05:00&quot;
ElseIf Format(Date, &quot;yyyy-mm-dd&quot >= Format(October, &quot;yyyy-mm-dd&quot Then
DST = &quot;-5&quot;
DST1stTime = &quot;04:59&quot;
DST2ndTime = &quot;05:00&quot;
Else
DST = &quot;-4&quot;
DST1stTime = &quot;03:59&quot;
DST2ndTime = &quot;04:00&quot;
End If


'DST1STtime and such are used for BETWEEN function...

Phailak

 

[vb6novice]

Phailak,

There is nothing wrong with your code. It takes you 32 lines of processing to do what could be done in 17 lines, but that difference and the speed at which it runs is probably inconsequential.

Here's my 17 lines (plus sub, end sub, and declarations) to do the same thing.

Sub get_time_shift()
Dim startDST, endDST As Date
Dim DST, DST1stTime, DST2ndTime

startDST = CDate(&quot;4/1/&quot; & Year(Date))
Do Until WeekDay(startDST) = 1
startDST = startDST + 1
Loop

endDST = CDate(&quot;10/31/&quot; & Year(Date))
Do Until WeekDay(endDST) = 1
endDST = endDST - 1
Loop

If Date >= startDST And Date <= endDST Then
DST = &quot;-4&quot;
DST1stTime = &quot;03:59&quot;
DST2ndTime = &quot;04:00&quot;
Else
DST = &quot;-5&quot;
DST1stTime = &quot;04:59&quot;
DST2ndTime = &quot;05:00&quot;
End If

End Sub

 

[Phailak]

Hail,

Hihihi, I was way too lazy to cut down the code, thanks. I still wish there was a simple function directly in access but for now this will do.

Phailak