Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cast as datetime question


Cast as datetime question

Author
Message
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Interesting question.

SQL DBA.
Adam-424116
Adam-424116
Say Hey Kid
Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)

Group: General Forum Members
Points: 691 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
john schroeder-334489
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

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
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 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++
Tony++
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 2004
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
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 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 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


TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10711 Visits: 12012
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search