Solved

Convert number (based on a fixed date value) to SQL Date

Posted on 2009-05-20
6
473 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:radpat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24434229
Seems like 2415020 is your baseline to date 0 in SQL server.
DECLARE @dateNum INT
SET @dateNum = 2429079
SELECT DATEADD(DAY, @dateNum - 2415020, 0)

Open in new window

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24434259
My approach:
SELECT DATEDIFF(DAY, 0, '1938-06-30') ==> gets 14059 which is difference in days from known number above to 1900-01-01 (0).

Take 2429079 - 14059 = 2415020.

Now using that you can mathematically get date by adding days less that number from 0 or just cast.
DECLARE @dateNum INT
SET @dateNum = 2430877
SELECT DATEADD(DAY, @dateNum - 2415020, 0)
SELECT CAST(@dateNum - 2415020 AS DATETIME)

Open in new window

0
 
LVL 42

Expert Comment

by:pcelba
ID: 24434321
SQL offers DATEADD() function:
SELECT DATEADD(day, 1798, '1938.06.30')
will result to June 2nd,  1943

So you may calculate almost all dates from your numbers by formula

DATEADD(day, YourNumber-YourConstant, 'YYYY.MM.DD')

YourConstant can be calculated from 1.1.1800 which should be 2378498

I've obtained this number using Visual FoxPro which has functions SYS(10) and SYS(11) - the FoxPro base of calculations is one day moved against your values but I hope the number  2378498 is calculated correctly (one day difference is included already).
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 42

Expert Comment

by:pcelba
ID: 24434416
Oops, the correction should be opposite:

select DATEADD(day, YourNumber-2378496, '1800.01.01')

But the solution from mwvisa1 works even for dates less than 1.1.1900

0
 
LVL 3

Author Comment

by:radpat
ID: 24435552
Thanks for the responses.  mwvisa1, I love your solution this is exactly what I needed.  Thanks!  pcelba, thanks for the response as well.  I didn't get a chance to try your solution but it does demonstrate how to use any date as a baseline.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24435850
Definitely.  I chose the approach I did as CAST uses '1900-01-01' as its baseline, so using that gives you ability to use direct conversions.  Glad that helped.

Regards,
kevin
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question