Dates stored in a Long variable

[RobCrombie]

I have had a little trick that I developed 7 years ago.
I convert dates relative to 2000 Jan 1 (actually 31st Dec 1999 as 0).
I can then store that offset in a long, rather than stuff around with Date variables. (Also avoid Date fields in the Data Base)

I just noticed that if you store Date into a Long, it does something similar.
Except that it appears to use 1900

 Dim myLong as Long
     myLong = Date

Does that mean some swine got the same idea, except he beat me by 100 years ?

[Merri]

It isn't really that someone would've thought about it before you, it is just a simple thing that happens due to the way Date works. Date is basically the same as Double, however, special handling on conversions are applied when Date is coerced to other datatypes (mostly when handling strings and date functions). But technically, Date is the same as Double.

What happens in behind is that numbers below the decimal point are reserved for representing the time of the day. So the very beginning of the day is 0 (equal to 0:00.00), but the very end of the day is 0.999999... and so on.

This leads into the fact that you increase a day by adding one, and you decrease a day by removing one. You can also jump days by half or 3/4 or whatever you wish, with simple math.

The downside with Long is that it can't handle as much data as Date can: it can't be even nearly as big as Date can (although when going to dates and values big enough, Date starts to lose accuracy, first on seconds, then on minutes, because there just isn't enough bits).

Here is a dummy example when using Currency (which can hold up to 4 digits below the decimal point).

Option Explicit

Private Sub Form_Load()
    Dim curTest As Currency, dtmLast As Date, lngCount As Long
    Const CURPIECE As Currency = 0.0001
    Const CUREND As Currency = 0.001
    
    Do Until curTest = CUREND
        If DateDiff("s", dtmLast, CDbl(curTest)) Then dtmLast = CDbl(curTest): lngCount = lngCount + 1: Debug.Print dtmLast
        curTest = curTest + CURPIECE
    Loop
    
    MsgBox lngCount
End Sub

Or a much simpler example:

Private Sub Form_Load()
    Dim dtmTest As Date
    dtmTest = 7 / 24
    MsgBox dtmTest
    MsgBox CDate(9 / 24)
End Sub

So there isn't anything really special going on in there.