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

T-SQL
1
2
3
4
5

declare @d datetime
select @d = getdate()
 
select @d  =@d+1
select @d
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

T-SQL
1
2
3
4
5

declare @d date
select @d = getdate()
 
select @d  =@d+1
select @d
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

T-SQL
1
2
3
4
5

declare @d date
select @d = getdate()
 
select @d  = dateadd(day,1,@d)
select @d
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?

T-SQL
1
2
3
4

declare @d date 
select @d = getdate() +1
 
select @d  
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

T-SQL
1
2

declare @d date = getdate() +1
select @d 
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

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Related Posts