Cast as datetime question

Question

Cast as datetime question

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

john.arnott SSChampion Points: 11882 More actions · Answer 1

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 SSC Guru Points: 104773 More actions · Answer 2

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