I have seen enough questions about this lately and this means that it is time for a blogpost. SQL Server 2008 has a bunch of new data types and one of them is the date datatype.
If you don’t care for the time portion of the date you can now use the date data type and save 5 bytes per row compared to datetime. I know that there is smalldatetime which only takes up 4 bytes but I myself could not use that because we have data that goes back to 1896 and thus can’t be stored in smalldatetime
So take a look at this code
declare @d datetime select @d = getdate() select @d =@d+1 select @d
To convert this to SQL Server 2008 logically you would think that all you had to do is change datetime to date. Go ahead…run it…make my day
declare @d date select @d = getdate() select @d =@d+1 select @d
And here is the message
Server: Msg 206, Level 16, State 2, Line 4
Operand type clash: date is incompatible with int
Now what you can do is use dateadd instead
declare @d date select @d = getdate() select @d = dateadd(day,1,@d) select @d
So that will work with both date and datetime (should work with all the date datatypes) and it is clear what you are doing.
But wait….scroll down in the next 5 seconds and you will get another option as a bonus.
What about this?
declare @d date select @d = getdate() +1 select @d
That does the addition before assignment.
But wait….scroll down in the next 5 seconds and you will get another option which is even shorter as a bonus.
Here is another version which is a little shorter
declare @d date = getdate() +1 select @d
Even though the last two versions are shorter, I would opt for using dateadd. With dateadd you know what you are doing, what does +1 mean? Are you adding days, hours or what…it is not clear from just looking at the code without running it