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