Use/Not Use of AddDate()

[Arnie Rowland]

A poster in another forum has asked what are the ramifications of just
adding 1 to a date (e.g., getdate() + 1) vs. using adddate().

While I clearly advise using adddate(), I realize that I don't have a good
response to the question of "what's the diff?"

Suggestions?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

[SQL Menace]

By saying adddate() do you mean dateadd() ;-(

select dateadd(d,1,getdate())

Denis the SQL Menace
http://sqlservercode.blogspot.com/

[Arnie Rowland]

Yep, meant dateadd()...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"Arnie Rowland" <ar...@1568.com> wrote in message
news:OWMY28t...@TK2MSFTNGP05.phx.gbl...

[SQL Menace]

The only thing i can think of is consistency, since you can only add
days with + and not months/quarters etc easily
With dateadd you can add months, quarter,years and more, so if you use
always dateadd at least you are consistent in that way IMHO

Denis the SQL Menace
http://sqlservercode.blogspot.com/

[Tibor Karaszi]

I'd add to that readability. If someone read "dtcol + 1", that person might think "1 what? Year,
day, second, hour?". IMO, it isn't intuitive that day is implied.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"SQL Menace" <denis...@gmail.com> wrote in message
news:1160757590....@i3g2000cwc.googlegroups.com...

[Anith Sen]

+ is an overloaded operator for numeric and character types meaning addition
and concatenation respectively. The only reason why GETDATE() + 1 works is
due to the internal representation of datetime values as two 4-byte values.

SELECT GETDATE() AS today,
CAST( GETDATE() AS VARBINARY )
AS "so called internal",
SUBSTRING( CAST( GETDATE() AS VARBINARY ) , 1 , 4 )
AS "days since 1900-01-01",
SUBSTRING( CAST( GETDATE() AS VARBINARY ) , 5 , 4 )
AS "ms since midnight",
CAST( SUBSTRING( CAST( GETDATE() AS VARBINARY ) , 1 , 4 ) AS INT )
AS "days as int",
CAST( SUBSTRING( CAST( GETDATE() AS VARBINARY ) , 5 , 4 ) AS INT )
AS "ms as int"

When you add integer 1 to a datetime value you are effectively adding one 4
byte value to the internally represented value. That is why "addition" seems
to be working. It also has a latent implicit conversion to datetime before
presenting the resulting value.

As a general recommendation, one should consider using the datetime type
operator DATEADD() rather than a numeric operator that relies on the
internal implementation of the type itself. Moreover it is generally
considered undocumented and this "feature" can disappear anytime in the
future.

And finally as you know, from a functional standpoint, using + allows only
adding days while DATEADD() can have various arguments starting from years
to milliseconds.

--
Anith

[Tibor Karaszi]

> The only reason why GETDATE() + 1 works is due to the internal representation of datetime values
> as two 4-byte values.

But it doesn't have to be that way. The ability to add days to datetime using the overloaded +
operator didn't exist until a fairly recent version (7.0, I believe). I just fired up SQL Server 1.1
and executed below:

DECLARE @a datetime
SELECT @a = getdate()
SELECT @a + 1

Above returns error message:

Operant type clash; int is incompatible with datetime
(Msg 206, Level 16, State 2).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:edNLwmu7...@TK2MSFTNGP05.phx.gbl...

[Arnie Rowland]

You've STILL got SQL v1.1 installed?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"Tibor Karaszi" <tibor_please.n...@hotmail.nomail.com> wrote in
message news:%23UOmS7v...@TK2MSFTNGP02.phx.gbl...

[Tibor Karaszi]

Yep. Nothing like firing up old SAF. :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"Arnie Rowland" <ar...@1568.com> wrote in message news:e$Xzrzw7...@TK2MSFTNGP03.phx.gbl...

[Anith Sen]

>> But it doesn't have to be that way. The ability to add days to datetime
>> using the overloaded + operator didn't exist until a fairly recent

>> version..

Exactly, and at times it looks like a classic illustration of confusing type
vs. internal representation. Even you can add decimal/fractional values too
like GETDATE() + (1./2), GETDATE() + .5 etc.

--
Anith