[AccessD] TimeSerial buggy for negative date values

Hi all

I brushed this bug up and nothing has changed for Access 2007.

Also, I believe I can tell why TimeSerial has been designed this way. The reason is that you with this behaviour can sum time values directly, because it for negative inputs returns a symmetrical negative value from time zero as this example demonstrates:

? TimeSerial(-18, 20, 0) + TimeSerial(18, -20, 0)
00:00:00 

But don't attempt to use DateDiff on these values:

? DateDiff("n", TimeSerial(-18, 20, 0), TimeSerial(18, -20, 0))
 1 

If you need to use DateDiff - which normally is the recommended method by Microsoft - use my TimeSerialFull or similar:

? DateDiff("n", TimeSerialFull(-18, 20, 0), TimeSerialFull(18, -20, 0))
 2120 

but, of course, don't attempt to add the values of TimeSerialFull directly:

? TimeSerialFull(-18, 20, 0) + TimeSerialFull(18, -20, 0)
12:40:00 

To conclude, this is probably a bug by intention but the documentation doesn't describe or explain this with a single word.

/gustav

>>> Gustav at cactus.dk 23-04-2006 16:29 >>>
Hi all

Did you know that for Access up to and including version 2003 return wrong date/time values when the parameters will result in a negative value?
This test shows that the returned value is a simple sequential numeric value which, however, provides faulty date values when these are negative.

Put in the debug window this line and press enter to obtain the output:

For i = 8 To -8 Step -1 : d = TimeSerialReal(0, 360 * i, 0) : ? CDbl(d), Format(d, "yyyymmdd hhnn") : Next
 2            19000101 0000
 1,75         18991231 1800
 1,5          18991231 1200
 1,25         18991231 0600
 1            18991231 0000

 0,75         18991230 1800
 0,5          18991230 1200
 0,25         18991230 0600
 0            18991230 0000
-0,25         18991230 0600
-0,5          18991230 1200
-0,75         18991230 1800

-1            18991229 0000
-1,25         18991229 0600
-1,5          18991229 1200
-1,75         18991229 1800
-2            18991228 0000

Two major traps are exposed here. 
First, date values between 0 and -1 (excluding) are invalid as they are already represented by their respective absolute equivalents. The positive values are the true values which can be demonstrated this way:

? CDbl(#1899-12-30 18:00#)
 0,75 

The consequence is serious a this results in a gap in the sequence of one day.
Second, the sequence of the decimal part of the date/time value must be reversed to adhere to the falling sequence.
Thus the sequence should read:
 2            19000101 0000
 1,75         18991231 1800
 1,5          18991231 1200
 1,25         18991231 0600
 1            18991231 0000

 0,75         18991230 1800
 0,5          18991230 1200
 0,25         18991230 0600
 0            18991230 0000

-1,75         18991229 1800
-1,5          18991229 1200
-1,25         18991229 0600
-1            18991229 0000

-2,75         18991228 1800
-2,5          18991228 1200
-2,25         18991228 0600
-2            18991228 0000

You may argue that you never use dates beyond 1900, but you will if you do operations on time values which may move backwards passing Midnight.
I don't know if this bug has survived until Access 12/2007. Perhaps a beta tester could confirm this?

A similar bug is present in DateAdd until Access 97. In Access 2000 it was corrected, which leads to a simple substitute for TimeSerial for Access 2000 an upwards:

Public Function TimeSerialReal( _
  ByVal intHour As Integer, _
  ByVal intMinute As Integer, _
  ByVal intSecond As Integer) _
  As Date
  
' Returns correct numeric negative date values,
' which TimeSerial() does not.
' This applies to Access 2007 and below.
' Requires Access 2000 or newer.
'
' 2006-04-23. Cactus Data ApS, CPH.
' 2008-08-10. Confirmed for A2007.

  Dim datTime As Date
  
  datTime = _
    DateAdd("h", intHour, _
    DateAdd("n", intMinute, _
    DateAdd("s", intSecond, #12:00:00 AM#)))
    
  TimeSerialReal = datTime
  
End Function

For Access 97 and all newer versions you can manipulate the output from TimeSerial with this function:

Public Function TimeSerialFull( _
  ByVal intHour As Integer, _
  ByVal intMinute As Integer, _
  ByVal intSecond As Integer) _
  As Date
  
  Dim datTime As Date
  Dim dblDate As Double
  Dim dblTime As Double
  
' Returns correct numeric negative date values,
' which TimeSerial() does not.
' This applies to Access 2007 and below.
'
' 2006-04-23. Cactus Data ApS, CPH.
' 2008-08-10. Confirmed for A2007.
'
' Example sequence:
'   Numeric      Date     Time
'   2            19000101 0000
'   1.75         18991231 1800
'   1.5          18991231 1200
'   1.25         18991231 0600
'   1            18991231 0000
'   0.75         18991230 1800
'   0.5          18991230 1200
'   0.25         18991230 0600
'   0            18991230 0000
'  -1.75         18991229 1800
'  -1.5          18991229 1200
'  -1.25         18991229 0600
'  -1            18991229 0000
'  -2.75         18991228 1800
'  -2.5          18991228 1200
'  -2.25         18991228 0600
'  -2            18991228 0000

  datTime = TimeSerial(intHour, intMinute, intSecond)
  If datTime < 0 Then
    ' Get date (integer) part of datTime shifted one day
    ' if a time part is present as Int() rounds down.
    dblDate = Int(datTime)
    ' Retrieve and reverse time (decimal) part.
    dblTime = dblDate - datTime
    ' Assemble and convert date and time part.
    datTime = CVDate(dblDate + dblTime)
  End If
    
  TimeSerialFull = datTime

End Function

/gustav