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
select dateadd(d,1,getdate())
Denis the SQL Menace
http://sqlservercode.blogspot.com/
--
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...
Denis the SQL Menace
http://sqlservercode.blogspot.com/
--
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...
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
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, 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, 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...
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