How do I fix this Date Issue

14 posts / 0 new
Last post
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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
Offline
Joined: 07/31/2012 - 13:20

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.

 

 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Thanks Steve for finding this and posting it.  We are all a bit smarter tonight.  Cool

Pages