How do I fix this Date Issue

sunlox

Forums

I have a very annoying issue with IDEA. I'm importing tables through SQL server and the dates come as numeric, a 6 digit number that starts with 7.
I realize there are a gazillion functions to convert numeric to date, but none of those actually work. They run but I end up with "ERROR" in place of the date records, in the date fields. 
Any other solution mentioned here or by IDEA doesn't work. But the issue can be fixed in Access or SQL Server, but not within IDEA.
Does anyone have an idea about this issue? What the IDEA help says about this doesn't work (apparently the numeric dates are in a format (not data type), that IDEA cannot understand)
In access I just use the CDate function minus a magic number, and that fixes it.

Brian Element Tue, 11/04/2014 - 08:44

Hi Sunlox, that is great to hear that things are working out even if you had to borrow the number from your SQL guy.

Actually the number I used was from taken from Sunlox's above equation.  I just recognized it was from the year 0000 or there about, unfortunately I did some google searchs and couldn't find how it is stored.  I guess in this case the proof is that Sunlox knows what date the number should change to so from there you can calculate the adjustment factor, also he was able to confirm this with his SQL contact.

Steven Luciani Tue, 11/04/2014 - 15:19

Below is from a post I found on an SQL message board by Dave Portas, SQL Server MVP, it explains the magic number 693596

The numbers represent the number of days since some base date. In your
case the numbers are based on day 1 being January 1 in the year 0. That
isn't a "real" date because the Gregorian calendar didn't exist until
the 16th century but even if it was a real date SQL Server can't handle
dates earlier than the year 1753. So the approach that Tom and I both
use is to adjust the numeric value by a fixed amount to bring it into
the acceptable range for SQL Server (based on 2000 in my case and 1900
in Tom's). Leap years are understood by the DATEADD function so as long
as the source system for the date values used the correct arithmetic
you will get the right date for any value between the years 1753 and
9999.

To clarify Dave's magic number is 730120 based on the year 2000, which doesn't work for IDEA as it starts its calculation from Jan 1, 1900, which is why we use Tom's magic number 693596.

What a great learning day.