Convert number (based on a fixed date value) to SQL Date
Posted on 2009-05-20
I have data that was extracted from an old btrieve database. I did not extract the data myself or have access to the original data. I initially received the data in a tab delimited flat text file format which was easily imported into tables onto SQL 2005.
That's when I noticed that all the date fields are compromised of values such as 2429079. At first I thought this was bad data due to a problem with the conversion. However after further inspection I am quite convinced they were stored this way as the numbers appear to be sequential based on days.
I was able to match dates to some of these numbers based on known values. So for example
June 30th, 1938 = 2429079
June 2nd, 1943 = 2430877
If you subtract the numbers and the dates you get the same value if you account for one extra day due to a leap year. So at least I know I'm not dealing with any time values but rather just days. Using on of the numbers mentioned above it seems the numbers are days counted up from a fixed date in the pass. Somewhere around 4700 BC (weird), it's hard to be sure the exact date since the numbers aren't dividing exactly by 365 (I'm sure leap years may have something to do with it).
SO HERE IS THE QUESTION:
Assuming I can find a base point in the past either by figuring out when the days started counting up or by finding the actual date of the smallest number in the dataset... Is there a way to then convert those numbers into a date?