Convert a VB Date

I need to read a flat-file “database” created in VB6 and convert it to an SQLite database. The flat-file stores dates in the VB date data type, which was essentially a double, with the date to the left of the decimal point and time to the right of it.

I was wondering if anybody had already tackled this and perhaps had some conversion code for VB Date -> Xojo (or SQL) Date?

Can you post a sample of the VB6 date format (what you would see back from the “database”)…

Here’s what it looks like: 42195.4519560185217415. It’s a Double (8 bytes) in the flat-file.

And what is the date and time of that?

Add that number of days to 01/01/1900 and you should be in the right ballpark.

Dim d As New Date(1900,1,1) d.day = d.day + 42195.4519560185217415

…should get you 11 July?

(the new framework uses .add for this incrementation, I gather…

Must be some proprietary format. I am not aware of anything resembling that in VB6 date.

If we knew what it is supposed to represent, maybe some reverse engineering would be possible.

It looks like a datetime stored as a double. I remember seeing something like it in MSAccess exports. Perhaps this could be of help.

The Access docs say 31/12/1899 = 1.0 so my 01/01/1900 is likely a day off.

[code]Dim d As New Date(1900,1,1) d.day = d.day + 42195.4519560185217415 - 1[/code]

or

Dim d As New Date(1899,12,31) d.day = d.day + 42195.4519560185217415

Thank you to all who jumped in on this one. The VB double 42195.45195601852174 represents 10 July 2015 at 10:50:49 am.

I found this site which, while last updated more than 15 years ago (!), covered the VB date issue in some detail. It turns out that VB dates start from 30 December 1899 so @Jeff Tullin you were pretty close! :slight_smile:

Using Jeff’s code for inspiration plus the link above I have this code in a method converting VB dates (as doubles) to Xojo Date objects in the classic framework:

dim daysPart as double = Floor(vbDateTime)
dim timePart as double = (vbDateTime - daysPart) * 86400
        
dim newDate As New Date(1899,12,30)
newDate.day  = newDate.day  +  daysPart
newDate.TotalSeconds = newDate.TotalSeconds + timePart

Just a further update to this to help anybody that might find it while searching for a similar solution.

While the code I posted above works fine on OS X, it doesn’t work under Windows (or at least it doesn’t work for me). Under Windows, the highest value for the vbDateTime double variable that works is 32,737 which creates the date 17 August 1989. But if vbDateTime is 32,738 the Xojo Date rolls back to 14 March 1810…

To fix it, I added code above the existing code to “seed” a different start date under Windows:

dim startYear as integer = 1899
dim startMonth as integer = 12
dim startDay as integer = 30
  
#if TargetWin32 then
    if vbDate > 32737 then
      vbDate = vbDate - 32737
      startYear = 1989
      startMonth = 8
      startDay = 17
    end
#endif

I also changed the way I construct newDate:

dim newDate As New Date(startYear, startMonth, startDay)