How many of us use some sort of date or datetime function when working with SQL Server? I cannot think of a single developer who would ever say they don’t work with datetime conversions in their application. Almost every application needs some manipulation of datetime datatypes. There are a number of pitfalls that can get into which we might not be aware. Here is a simple blog: Quiz with DATEADD Function that I would love to hear your views on what the output would be:
Quick Trivia – DATEADD Function
Make your guess to what is the value for the below query? Guess before executing the same on SQL Server Management Studio.
SELECT DATEADD(MONTH, 1, '2015-01-28') SELECT DATEADD(MONTH, 1, '2015-01-29') SELECT DATEADD(MONTH, 1, '2015-01-30') SELECT DATEADD(MONTH, 1, '2015-01-31')
Do you see something strange in the output? Will you be able to explain why we are getting this output? Why are the values so similar? I am sure once you execute the query in SSMS – the answer will be easy.
If the output is still confusing, hang on. Now what would be the values for the below query?
SELECT DATEADD(MONTH, 1, '2016-01-28') SELECT DATEADD(MONTH, 1, '2016-01-29') SELECT DATEADD(MONTH, -1, '2015-03-30') SELECT DATEADD(MONTH, -1, '2015-03-31')
It is important to note is that DATEADD can either use a positive or negative integer as part of adding value. I am sure, I did trick you with the above query. Now the explanation becomes easy. Write your explanation in the comments and I will try to give you a special prize of one month free subscription to Pluralsight for five correct valid comments to this question.
Part 2
As I wrap up this blog, I would like to show how one of my friends made a small mistake while using the DATEADD function and how they got unexpected results. While using DATEADD, they accidentally wrote a YEAR function instead of “month”.
SELECT DATEADD(YEAR, 8000, '2015-01-31');
If you execute the above query, we will be presented with an Error. This is part II of the trivia. This will be an added bonus if you can explain.
Msg 517, Level 16, State 1, Line 9
Adding a value to a ‘datetime’ column caused an overflow.
Call to Action
1) Write the answer to first quiz – Why specific outcome when you execute queries with DateAdd and month?
2) Write the answer to second quiz – Why specific error on the screen?
Leave answer before February 20th, 2015 in comment section to eligible for price of free Pluralsight subscription.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
