By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Standard Time calculation wrong in PreciseDateDiff .... fix

P: n/a
I have used the code provided in the PreciseDateDiff function at the
following Access Web link to calculate the time change date (from
Daylight to Standard time and vice versa):

http://www.mvps.org/access/api/api0024.htm

But looks like for this week only, there is a small error in the
calculation in the 'GetSundate' function included in the code. You
can see what I mean by adding the code below (copied directly from the
above link) to an Access module. Then in the debugger, enter:

?StandardTime(2003)

The supposed StandardTime change date will display as"
11/2/2003 2:00:00 AM

which is obviously not correct. It works correctly for the year 2000
and 2004, but the years between are wrong.

The problem stems from the following line:

varRet = DateAdd("ww", intSun - 1, varRet)

I believe the 'GetTimeZoneInformation' call to the operating system
returns a value of 5 for intSun in October, and the above blindly adds
4 weeks regardless of the date. Consequently, the date rolls into
November sometimes. I have added the following 'if' statement to
change the value to a 4 if this happens:

If intSun = 5 And Month(DateAdd("ww", intSun - 1, varRet)) = 11 Then
intSun = 4
End If
varRet = DateAdd("ww", intSun - 1, varRet)
Just thought others may be interested. I don't know if there is a
more elegant solution.

NOTE: For users who just use the PreciseDateDiff function as is (to
calculate date differences), it may not make a difference, since the 2
calulations will cancel each other, but I needed to have the date of
the change.

Hope this might help anyone else that is using it.

Terry.
'************************** Code Start ***********************
'This code was originally written by Terry Kreft & Michel Walsh
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft & Michel Walsh
'
Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(31) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(31) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Public Function StandardTime(Optional intYear As Long = -1) As Date
' Originally submitted by Terry Kreft
' modified to accept an optinal year

If -1 = intYear Then intYear = Year(Date)
' Get this year, by defaut, not -1

Dim lngRet As Long
Dim TZI As TIME_ZONE_INFORMATION
lngRet = GetTimeZoneInformation(TZI)
With TZI.StandardDate
StandardTime = CVDate(GetSundate(.wMonth, .wDay, _
intYear) + (.wHour / 24))
End With
End Function

Private Function GetSundate(intMonth As Integer, _
intSun As Integer, _
Optional intYear As Long = -1) _
As Date
' Originally submitted by Terry Kreft
' Modified to set any Year

If intYear = -1 Then intYear = Year(Date)
' if not supplied, get this Year

Dim varRet As Variant
Dim intDayOfWeek As Integer

varRet = DateSerial(intYear, intMonth, 1)
' avoid regional setting problem

intDayOfWeek = Weekday(varRet)
If intDayOfWeek <> 1 Then
varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
End If
varRet = DateAdd("ww", intSun - 1, varRet)
GetSundate = varRet
End Function




Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The code doesn't blindly add 5 what it's doing is using the week offset
(which happens to be 5 in this case for the UK at least) to calculate when
the last day is.

Looking at the documentation again, if a value of 5 is returned from the API
call this actually means the last week of the month rather than an offset
from the start of the month.

So you could do something like

Private Function GetSundate(intMonth As Integer, _
intSun As Integer, _
Optional intYear As Long = -1) _
As Date
' Originally submitted by Terry Kreft
' Modified to set any Year

If intYear = -1 Then intYear = Year(Date)
' if not supplied, get this Year

Dim varRet As Variant
Dim intDayOfWeek As Integer
Select Case intSun
Case 5 ' last week of month
varRet = DateSerial(intYear, intMonth + 1, 1)
' avoid regional setting problem

intDayOfWeek = WeekDay(varRet)
If intDayOfWeek <> 1 Then
varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
End If
varRet = DateAdd("ww", -1, varRet)

Case Else
varRet = DateSerial(intYear, intMonth, 1)
' avoid regional setting problem

intDayOfWeek = WeekDay(varRet)
If intDayOfWeek <> 1 Then
varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
End If
varRet = DateAdd("ww", intSun - 1, varRet)
End Select
GetSundate = varRet
End Function

Terry Kreft
"Terry" <sa********@shaw.ca> wrote in message
news:o3********************************@4ax.com...
I have used the code provided in the PreciseDateDiff function at the
following Access Web link to calculate the time change date (from
Daylight to Standard time and vice versa):

http://www.mvps.org/access/api/api0024.htm

But looks like for this week only, there is a small error in the
calculation in the 'GetSundate' function included in the code. You
can see what I mean by adding the code below (copied directly from the
above link) to an Access module. Then in the debugger, enter:

?StandardTime(2003)

The supposed StandardTime change date will display as"
11/2/2003 2:00:00 AM

which is obviously not correct. It works correctly for the year 2000
and 2004, but the years between are wrong.

The problem stems from the following line:

varRet = DateAdd("ww", intSun - 1, varRet)

I believe the 'GetTimeZoneInformation' call to the operating system
returns a value of 5 for intSun in October, and the above blindly adds
4 weeks regardless of the date. Consequently, the date rolls into
November sometimes. I have added the following 'if' statement to
change the value to a 4 if this happens:

If intSun = 5 And Month(DateAdd("ww", intSun - 1, varRet)) = 11 Then
intSun = 4
End If
varRet = DateAdd("ww", intSun - 1, varRet)
Just thought others may be interested. I don't know if there is a
more elegant solution.

NOTE: For users who just use the PreciseDateDiff function as is (to
calculate date differences), it may not make a difference, since the 2
calulations will cancel each other, but I needed to have the date of
the change.

Hope this might help anyone else that is using it.

Terry.
'************************** Code Start ***********************
'This code was originally written by Terry Kreft & Michel Walsh
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft & Michel Walsh
'
Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(31) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(31) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Public Function StandardTime(Optional intYear As Long = -1) As Date
' Originally submitted by Terry Kreft
' modified to accept an optinal year

If -1 = intYear Then intYear = Year(Date)
' Get this year, by defaut, not -1

Dim lngRet As Long
Dim TZI As TIME_ZONE_INFORMATION
lngRet = GetTimeZoneInformation(TZI)
With TZI.StandardDate
StandardTime = CVDate(GetSundate(.wMonth, .wDay, _
intYear) + (.wHour / 24))
End With
End Function

Private Function GetSundate(intMonth As Integer, _
intSun As Integer, _
Optional intYear As Long = -1) _
As Date
' Originally submitted by Terry Kreft
' Modified to set any Year

If intYear = -1 Then intYear = Year(Date)
' if not supplied, get this Year

Dim varRet As Variant
Dim intDayOfWeek As Integer

varRet = DateSerial(intYear, intMonth, 1)
' avoid regional setting problem

intDayOfWeek = Weekday(varRet)
If intDayOfWeek <> 1 Then
varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
End If
varRet = DateAdd("ww", intSun - 1, varRet)
GetSundate = varRet
End Function



Nov 12 '05 #2

P: n/a
Thanks for reviewing it Terry,

That works too (even in Canada!)..

Cheers.

Terry H.
On Thu, 30 Oct 2003 16:45:02 -0000, "Terry Kreft"
<te*********@mps.co.uk> wrote:
The code doesn't blindly add 5 what it's doing is using the week offset
(which happens to be 5 in this case for the UK at least) to calculate when
the last day is.

Looking at the documentation again, if a value of 5 is returned from the API
call this actually means the last week of the month rather than an offset
from the start of the month.

So you could do something like

Private Function GetSundate(intMonth As Integer, _
intSun As Integer, _
Optional intYear As Long = -1) _
As Date
' Originally submitted by Terry Kreft
' Modified to set any Year

If intYear = -1 Then intYear = Year(Date)
' if not supplied, get this Year

Dim varRet As Variant
Dim intDayOfWeek As Integer
Select Case intSun
Case 5 ' last week of month
varRet = DateSerial(intYear, intMonth + 1, 1)
' avoid regional setting problem

intDayOfWeek = WeekDay(varRet)
If intDayOfWeek <> 1 Then
varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
End If
varRet = DateAdd("ww", -1, varRet)

Case Else
varRet = DateSerial(intYear, intMonth, 1)
' avoid regional setting problem

intDayOfWeek = WeekDay(varRet)
If intDayOfWeek <> 1 Then
varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
End If
varRet = DateAdd("ww", intSun - 1, varRet)
End Select
GetSundate = varRet
End Function

Terry Kreft
"Terry" <sa********@shaw.ca> wrote in message
news:o3********************************@4ax.com.. .
I have used the code provided in the PreciseDateDiff function at the
following Access Web link to calculate the time change date (from
Daylight to Standard time and vice versa):

http://www.mvps.org/access/api/api0024.htm

But looks like for this week only, there is a small error in the
calculation in the 'GetSundate' function included in the code. You
can see what I mean by adding the code below (copied directly from the
above link) to an Access module. Then in the debugger, enter:

?StandardTime(2003)

The supposed StandardTime change date will display as"
11/2/2003 2:00:00 AM

which is obviously not correct. It works correctly for the year 2000
and 2004, but the years between are wrong.

The problem stems from the following line:

varRet = DateAdd("ww", intSun - 1, varRet)

I believe the 'GetTimeZoneInformation' call to the operating system
returns a value of 5 for intSun in October, and the above blindly adds
4 weeks regardless of the date. Consequently, the date rolls into
November sometimes. I have added the following 'if' statement to
change the value to a 4 if this happens:

If intSun = 5 And Month(DateAdd("ww", intSun - 1, varRet)) = 11 Then
intSun = 4
End If
varRet = DateAdd("ww", intSun - 1, varRet)
Just thought others may be interested. I don't know if there is a
more elegant solution.

NOTE: For users who just use the PreciseDateDiff function as is (to
calculate date differences), it may not make a difference, since the 2
calulations will cancel each other, but I needed to have the date of
the change.

Hope this might help anyone else that is using it.

Terry.
'************************** Code Start ***********************
'This code was originally written by Terry Kreft & Michel Walsh
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft & Michel Walsh
'
Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(31) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(31) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Public Function StandardTime(Optional intYear As Long = -1) As Date
' Originally submitted by Terry Kreft
' modified to accept an optinal year

If -1 = intYear Then intYear = Year(Date)
' Get this year, by defaut, not -1

Dim lngRet As Long
Dim TZI As TIME_ZONE_INFORMATION
lngRet = GetTimeZoneInformation(TZI)
With TZI.StandardDate
StandardTime = CVDate(GetSundate(.wMonth, .wDay, _
intYear) + (.wHour / 24))
End With
End Function

Private Function GetSundate(intMonth As Integer, _
intSun As Integer, _
Optional intYear As Long = -1) _
As Date
' Originally submitted by Terry Kreft
' Modified to set any Year

If intYear = -1 Then intYear = Year(Date)
' if not supplied, get this Year

Dim varRet As Variant
Dim intDayOfWeek As Integer

varRet = DateSerial(intYear, intMonth, 1)
' avoid regional setting problem

intDayOfWeek = Weekday(varRet)
If intDayOfWeek <> 1 Then
varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
End If
varRet = DateAdd("ww", intSun - 1, varRet)
GetSundate = varRet
End Function




Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.