[AccessD] TimeSerial buggy for negative date values

Hi Stuart

Thanks! I understand what you say, but as I read it, I think you missed my point ...

> You are storing two separate things here, a date and a time 
> - not an offset from a zero point.

That's true, that's how the data type Date is designed, and that's my key point. 

But TimeSerial() does not do so; it returns an offset from a zero point. That zero point is numeric zero which is equivalent to 1899-12-30 00:00:00. For positive values that's correct, but not for negative. Further, some of these values (between 0 and -1) are invalid as they can not be expressed by a #..# construct though VBA will interpret these values as if they were positive:

 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

However, according to this excellent article (time to brush up your German!):

  http://www.vbarchiv.net/archiv/tipp_details.php?pid=1308

these values between -1 and 0 could be regarded as true in some way and should simply be read by their absolute value which, in fact, is what VB(A) does.

Still, TimeSerial() will also deliver values below -1 as an offset from a zero point which is buggy.
Consider these small examples:

? DateDiff("h", TimeSerial(-6, 0, 0), TimeSerial(6, 0, 0))
 1 
? DateDiff("h", TimeSerial(-18, 0, 0), TimeSerial(18, 0, 0))
 1 

The results should, of course, be 12 and 36 which my modified function returns:

? DateDiff("h", TimeSerialFull(-6, 0, 0), TimeSerialFull(6, 0, 0))
 12 
? DateDiff("h", TimeSerialFull(-18, 0, 0), TimeSerialFull(18, 0, 0))
 36

> 1. There is no such thing as "0" for the integer part ie  no such as "1 Jan 
> 0000" or "31 Dec 0000". Using the microsoft representation, there is no 
> such thing as "Day 0"  or times within such a day.

There is. But it is not of year "0" as that year, as you correctly states, does not exist. It has been chosen by MS to represent the date of 1899-12-30. Probably because it allows data type Date to hold up to 9999-12-31 23:59:59 within the range and resolution of the IEEE 64 bit data type Double.
For several reasons MS has chosen 100-01-01 as the earliest date for the data type Date even though the capacity of data type Double allows for much earlier dates. One reason is that if years of 99 and earlier were allowed, you couldn't type in two-digits years for present dates.

> 2. Fractional parts are always positive, they indicate the amount of the 
> specified unit (year or day) that has elapsed.

Well, yes and no.
For dates before 1899-12-30 the numeric value is negative, thus the time part is negative as well. And a "larger" time part means a more negative date value:

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

and for a good reason: In this way the integer part of a date value will always represent the date.
To extract the time part you have to isolate it (remove the integer part) and turn it positive:

      ' Split datBase in date and time parts.
      dblDate = Fix(datDate)
      dblTime = Abs(datDate - dblDate)

Also, look up my related and recent post on "Sorting old dates" from 2006-04-06 13:41:52 which shows how to sort correctly numeric negative date values.

To summarize and illustrate, here is a sequential sorted list with 
 - date/times around the numeric zero value
 - the numeric equivalent if this was linear
 - the actual (native) numeric equivalent from CDbl(datDate):

'   Date     Time  Linear        Native
'   19000101 0000  2             2
'
'   18991231 1800  1,75          1,75
'   18991231 1200  1,5           1,5
'   18991231 0600  1,25          1,25
'   18991231 0000  1             1
'
'   18991230 1800  0,75          0,75
'   18991230 1200  0,5           0,5
'   18991230 0600  0,25          0,25
'   18991230 0000  0             0
'
'   18991229 1800 -0,25         -1,75
'   18991229 1200 -0,5          -1,5
'   18991229 0600 -0,75         -1,25
'   18991229 0000 -1            -1
'
'   18991228 1800 -1,25         -2,75
'   18991228 1200 -1,5          -2,5
'   18991228 0600 -1,75         -2,25
'   18991228 0000 -2            -2

/gustav


>>> stuart at lexacorp.com.pg 23-04-2006 23:25:18 >>>
On 23 Apr 2006 at 16:29, Gustav Brock wrote:

> 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?

Not wrong values, just values that you are not expecting. It's not a bug, 
it's a feature and it is in fact a feature of all calendars, it's not a 
microsoft thing. 

<quote>
Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers 
that represent dates ranging from 1 January 100 to 31 December 9999 and 
times from 0:00:00 to 23:59:59.
...
Negative whole numbers represent dates before 30 December 1899.
</quote>

You are storing two separate things here, a date and a time - not an offset 
from a zero point.

So:
1.25 represent 6:00am on Day 1
-1.25 represents 6:00am on Day -1
0.25 is meaningless. There is no Day 0.

That is what happens in any calendar system. Think of it using our calendar 
with years as the integer part rather than days.

1 Jan 0001 AD = (Year 1) + 1/365 years
The previous day is:
 31 Dec 0001 BC = (Year -1) + 364/365 days

Key points are:
1. There is no such thing as "0" for the integer part ie  no such as "1 Jan 
0000" or "31 Dec 0000". Using the microsoft representation, there is no 
such thing as "Day 0"  or times within such a day.

2. Fractional parts are always positive, they indicate the amount of the 
specified unit (year or day) that has elapsed.

-- 
Stuart