Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Cast as datetime question Expand / Collapse
Author
Message
Posted Wednesday, November 18, 2009 9:54 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:03 AM
Points: 3,924, Visits: 1,589
Interesting question.

SQL DBA.
Post #820972
Posted Wednesday, November 18, 2009 1:55 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 20, 2010 2:06 PM
Points: 683, Visits: 185
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!
Post #821174
Posted Wednesday, November 18, 2009 3:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 2:33 PM
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.
Post #821229
Posted Wednesday, November 18, 2009 4:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
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.
Post #821291
Posted Thursday, November 19, 2009 7:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 1,417, Visits: 1,526
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.
Post #821614
Posted Thursday, November 19, 2009 9:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
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 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                    
--------------------------- ---------------------------
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 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


Post #821750
Posted Friday, January 22, 2010 2:20 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
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
Post #852349
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse