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
5 Comments
Great post Denis!
The question that needs to be asked to the individuals having these types of problems is should they use them if they do not educate themselves on them first.
Date is a perfect example. Great and long awaited data type. Let’s use it NOW!!! Oh wait, all my code doesn’t work any more.
Just because the button is there doesn’t mean you press it before finding out what it does “completely” 🙂
Bummer – I use that.
Explicit conversion from data type int to date is not allowed.
Very good explanation. I’m working on one task and I get this error, “operance type clash: date is incompatible with XML”. Any better explanation for this please.
Thanks
Great explanation. Solved my problem instantly….