Cast as datetime question

 Author Message SanjayAttray SSCertifiable Group: General Forum Members Points: 5621 Visits: 1619 Interesting question. SQL DBA. Adam-424116 Right there with Babe Group: General Forum Members Points: 797 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=1900-1-1, but in Excel 1=1900-1-1. The second is that EXCEL incorrectly thinks 1900 was a leap year. So, after 59 (1900-03-01 in SQL, 1900-02-28 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! john schroeder-334489 Valued Member Group: General Forum Members Points: 64 Visits: 55 Adam-424116 (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=1900-1-1, but in Excel 1=1900-1-1. The second is that EXCEL incorrectly thinks 1900 was a leap year. So, after 59 (1900-03-01 in SQL, 1900-02-28 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=1900-1-1.See [url=http://support.microsoft.com/kb/214326/en-us ][/url] for an explanation of the Excel leap year error. john.arnott Hall of Fame Group: General Forum Members Points: 3510 Visits: 3059 EXCEL incorrectly thinks 1900 was a leap year. Thank you, Adam-424116! 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. Tony++ SSCrazy Group: General Forum Members Points: 2645 Visits: 2057 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. john.arnott Hall of Fame Group: General Forum Members Points: 3510 Visits: 3059 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/en-us/library/ms178565(SQL.90).aspx):B. Using the addition operator to add days to date and time valuesThe following example adds a number of days to a datetime date.`USE master;GOSET NOCOUNT ONDECLARE @startdate datetime, @adddays intSET @startdate = '1/10/1900 12:00 AM'SET @adddays = 5SET NOCOUNT OFFSELECT @startdate + 1.25 AS 'Start Date', @startdate + @adddays AS 'Add Date'`Here is the result set. `Start Date Add Date --------------------------- --------------------------- 1900-01-11 06:00:00.000 1900-01-15 00:00:00.000(1 row(s) affected)`This works as the datetime type is actually a pair of 4-byte 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/en-us/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 re-converting to datetime.`-- Using datetime arithmetic to isolate the date or time portion-- is more efficient than CONVERT or CAST for comparison operationsdeclare @mydttm datetimeset @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` Tom Thomson One Orange Chip Group: General Forum Members Points: 25306 Visits: 12488 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