Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 – implicit date addition

I am a pretty lazy DBA, and  I use the UI wherever I can to get the job done as simply and quickly as possible, but laziness can be taken to far especially when it comes to dates..

It is possible to write a query like this

select getdate() + 1

..which adds a day to the current date which is known as an implicit add. If you try this

select sysdatetime() + 1’ll get the same thing unless  you are using SQL Server 2008 in which case you’ll get the error

Operand type clash: datetime2 is incompatible with int

This doesn’t work as the implicit add has been deliberately disabled for the new date & time data types in SQL Server 2008 and sysdatetime is one of these (datetime2). Why chnage its data type? Because of its increased precision.

So the customer who brought this up has got to either use the first query (which still works with SQL Server 2008) or in my opinion write something a little longer like

select DATEADD(DAY,1,sysdatetime())

..which is not only just as quick to run, but can be understood by humans and making it easier to maintain and document.

Other lazy things to avoid are not fully qualifying objects e.g. use mydb.myschema.mytable and eliminate the use of * as in select * from mytable especially in SQL Server 2008 as we have intellisense!