

Hall of Fame
Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924,
Visits: 1,607


Interesting question.
SQL DBA.




Say Hey Kid
Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:23 AM
Points: 683,
Visits: 192


Dan Guzman  Not the MVP (11/18/2009) Interesting, I got this wrong because in Excel 434115 = July 24th 3088, not the 26th. Why there is a difference of 2 days over a 1100+ year gap is beyond me. Sum of leap minutes?
This made me curious, so I decided to look into it. I copied the results of a query on my numbers table (converting each number to it's corresponding date) into Excel, and converted the "number" column to a date column. It appears that this inconsistency is due to two differences. The first is that in SQL 0=190011, but in Excel 1=190011. The second is that EXCEL incorrectly thinks 1900 was a leap year. So, after 59 (19000301 in SQL, 19000228 in EXCEL), all of the first 1,000,000 integers to dates are off by 2. (I didn't check beyond 1,000,000, but I'm guessing it holds true until other boundary conditions apply).
So, I suppose this is a cautionary tale of how to transfer date/time data between systems!




Valued Member
Group: General Forum Members
Last Login: Tuesday, September 28, 2010 2:33 PM
Points: 64,
Visits: 55


Adam424116 (11/18/2009)
Dan Guzman  Not the MVP (11/18/2009) Interesting, I got this wrong because in Excel 434115 = July 24th 3088, not the 26th. Why there is a difference of 2 days over a 1100+ year gap is beyond me. Sum of leap minutes?This made me curious, so I decided to look into it. I copied the results of a query on my numbers table (converting each number to it's corresponding date) into Excel, and converted the "number" column to a date column. It appears that this inconsistency is due to two differences. The first is that in SQL 0=190011, but in Excel 1=190011. The second is that EXCEL incorrectly thinks 1900 was a leap year. So, after 59 (19000301 in SQL, 19000228 in EXCEL), all of the first 1,000,000 integers to dates are off by 2. (I didn't check beyond 1,000,000, but I'm guessing it holds true until other boundary conditions apply). So, I suppose this is a cautionary tale of how to transfer date/time data between systems!
The same is true for MS Access 1=190011.
See [url=http://support.microsoft.com/kb/214326/enus ][/url] for an explanation of the Excel leap year error.




Ten Centuries
Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388,
Visits: 3,039


EXCEL incorrectly thinks 1900 was a leap year.
Thank you, Adam424116!
This seemed so strange that I had to check and sure enough, even in Office 2008, Excel has 2/29/00, internally 60. I could hardly believe that MS hasn't considered this error, so checked their web site and found this "explanation" and instructions on adjusting dates before 1 March 1900 (http://support.microsoft.com/kb/214058/):
When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.
However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.




SSCommitted
Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 1,531,
Visits: 1,624


As shown in this QOTD, adding/subtracting a number from a date is adding/subtracting that many whole days. Is this standard behavior, or is this undocumented behavior that could change in the future (to minutes/weeks/whatever)?
I've wondered about this before, and so have always stuck with using DATEADD to be sure my code is safe.




Ten Centuries
Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388,
Visits: 3,039


Tony++ (11/19/2009) As shown in this QOTD, adding/subtracting a number from a date is adding/subtracting that many whole days. Is this standard behavior, or is this undocumented behavior that could change in the future (to minutes/weeks/whatever)?
I've wondered about this before, and so have always stuck with using DATEADD to be sure my code is safe. Good question. MS does say it's OK. See documentation on using the TSQL +(Add) arithmetic operator (http://msdn.microsoft.com/enus/library/ms178565(SQL.90).aspx):
B. Using the addition operator to add days to date and time values The following example adds a number of days to a datetime date. USE master; GO SET NOCOUNT ON DECLARE @startdate datetime, @adddays int SET @startdate = '1/10/1900 12:00 AM' SET @adddays = 5 SET NOCOUNT OFF SELECT @startdate + 1.25 AS 'Start Date', @startdate + @adddays AS 'Add Date' Here is the result set. Start Date Add Date   19000111 06:00:00.000 19000115 00:00:00.000
(1 row(s) affected) This works as the datetime type is actually a pair of 4byte integers, the first of which contains the number of days before or after 1/1/1900 and the second of which contains the number of 1/3 millisecond intervals after midnight (see http://msdn.microsoft.com/enus/library/ms187819(SQL.90).aspx). This allows us to use simple arithmetic on the date portion and also facilitates a handy technique for splitting the date and time portions from a single datetime without the greater overhead of converting to a string, parsing that and reconverting to datetime.
 Using datetime arithmetic to isolate the date or time portion  is more efficient than CONVERT or CAST for comparison operations
declare @mydttm datetime set @mydttm = '1857/02/12 13:00'
select @mydttm as orig ,DateAdd(dd, 0, Datediff(dd, 0, @mydttm)) as justdate ,@mydttm  DateAdd(dd, 0, Datediff(dd, 0, @mydttm)) as justtime




SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791,
Visits: 9,545


It's quite a good question, but I think the explanation of the answer could have been a bit better, since some may have reached the right answer for the wrong reason (or for insufficient reason) and need educating, and some will have got it wrong and want to know how one would go about getting it right (short of cheating and running the query).
The three ways to get it right are to do some pretty awful arithmetic, to cheat by running in in management studio, or eliminate obviously wrong answers until there's only one answer left. The first is not something that most people would want to contemplate, while the second is cheating. The last approach works just fine. However the explanation might usefully have made some of the points that working out the answer by that approach has to go through  for example that the range of the datetime type is 8247 years, which is more than 435000 days so the integer to be cast isn't out of range; then more people would be likely to learn from it.
Tom



