Adding and subtracting with date

Question

Adding and subtracting with date

FreeHansje

SSChampion

Points: 11751
More actions
November 5, 2007 at 4:55 am

#62976

Hi there,
I am fully aware of DATEADD and DATEDIFF functions.
However, for something simple like adding or subtracting I can easily write something like:
SELECT getdate() - 7
which will get me the date from a week ago.
But, now I do this:
SELECT '20071105'-7
with result: 20071098....
When doing this:
SELECT '2007-11-05'-7
I receive error: Syntax error converting the varchar value '2007-11-05' to a column of data type int.
Which I find strange, for this:
select CONVERT(datetime,'2007-11-05')-7
will result in a correct date.
Now, when I write this:
declare @datum datetime
select @datum = getdate()
select @datum-30
I receive a correct date as a result.
Can anyone shed some light on this? I am now unsure what format I can/must use with the - or +operator.
Greetz,
Hans Brouwer

Viewing 3 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

by default SQL server treats anything between single quotes as a varchar; it' doesn't try to determine whether the varchar may also be a date...that's the behavior you are seeing.

so SELECT '2007-12-11' + 30 is treated the same way as 'Bob Smith' + 30, and you get an error converting a varchar to an int for the purpose of the addition/subtraction.

As you saw, whenever the value was clearly cast/converted/datatyped as a date, you got your expected behaviour, as there was no confusion on it's type.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

Its all about the precedence of data types in operations.

SELECT '20071105'-7
with result: 20071098....

...The precedence of numerical values is higher than the character values. This means the value '20071105' is converted to 20071105, then the subtraction takes place.

When doing this:
SELECT '2007-11-05'-7
I receive error: Syntax error converting the varchar value '2007-11-05' to a column of data type int.

...As I said above, the optimizer tries the convert the string '2007-11-05' to a numeric value, which it fails to convert and throws the error.

Which I find strange, for this:
select CONVERT(datetime,'2007-11-05')-7
will result in a correct date.

...Here the precedence the given to datetime value and the default subtraction of 7 days takes place.

...For precision table, check BOL

...The addition or subtraction operations involving datetime and integer values forces the optimizer to treat the integer value as the day part.

--Ramesh