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://blog.sqlauthority.com)
61 Comments. Leave new
For first quiz outputs are same because in Feb-2015 there are max 28 days and in your queries you are adding 1 month to 28 Jan 2015 – 31st Jan 2015. whereas if you run SELECT DATEADD(MONTH, 1, ‘2015-01-27’) you will get 2015-02-27 00:00:00.000.
coz output of dateadd and month query depends on no of days in that specific month or n th no of day or last day of month… not on numeric value
For second quiz, getting overflow error because Max date in SQL is 31st Dec 9999 whereas if you trying to execute SELECT DATEADD(YEAR, 8000, ‘2015-01-31′) where you are trying to add 8000 yrs to 2015 i.e. 10015 which is more than max sql date. So for year 2015 you can add max 7984 yrs.
Execute SELECT DATEADD(YEAR, 7984 ,’2015-01-31’); Output will be 9999-01-31 00:00:00.000.
The reason is the Month Feb 2015, has 28 days ..So results of adding a month to January dates, would be feb with 28 days.
Where as Feb, 2016 has 29 days.
1) The reason is the Month Feb 2015, has 28 days ..So results of adding a month to January dates, would be feb with 28 days.
Where as Feb, 2016 has 29 days.
2) The YEAR format is ‘YYYY’, adding 8000 to 2015 (8000+2015 = 10015, which is YYYYY format), which results in error
Answer for 1st one. It is very simple. Feb has only 28 days in the 2015 since it is not leap year. When you try to do any action above or equal to 28 the answer is going to same.
Whereas if we try with 2016 it is going to yield different answer for 1 and rest 3 as same.
For Ex: This is also going to yield the same result.
SELECT DATEADD(MONTH, 1, ‘2015-03-30’)
SELECT DATEADD(MONTH, 1, ‘2015-03-31’)
Answer for the second one is it is going to be 5 digit number when you add 8000 to 2015 i.e. 10015 currently the engine supports only “YYYY” format. Hence it is throwing overflow exception.
Because the date format is fixed between
Date range
January 1, 1753, through December 31, 9999
—
SELECT convert(varchar, DATEPART(year,’2015-01-31′)+8000) +’-‘+
convert(varchar, DATEPART(day,’2015-01-31′))+’-‘+
convert(varchar, DATEPART(month,’2015-01-31’))
—-
We can try this.
Part 1 – Since 2015 is not a leap year because of which february month contains only 28 days. So, when you add the dates 2015-01-29, 2015-01-30 and 2015-01-31, the dateadd function (ATEADD(MONTH, 1, ‘2015-01-31’) ) will default to the last date of the month and gives the output as 28-02-2015. And also, 2016 is a leap year and february month as 29 days, therefore it will give the output for DATEADD(MONTH, 1, ‘2016-01-29’) as 2016-02-29 but using date as DATEADD(MONTH, 1, ‘2016-01-30’)will default to 2016-02-29.
Part 2 – Since the maximum value for the year is defaulted to 9999 in SQL due to which it is unable to add the value 8000 to the current year 2015. And further, 8000+2015 = 10015 which is greater than 9999. So the maximum value you can add is 7984 which will result in to 9999.
SELECT DATEADD(YEAR, 7984, ‘2015-01-31’);
Output is :-
9999-01-31 00:00:00.000
Hello Pinal,
Question 1 ) – It is because FEB 2015 contains 278 days only , hence even if you added a month to 29th,30th or 31st Jan it will always show Give Feb 28.
However For Feb 2016, being a Leap year, the value can be 29th Feb
Question 2 ) – SQL server date allows to store year in fout digit format ‘yyyy-MM-dd’
SELECT DATEADD(YEAR, 8000, ‘2015-01-31’); tries to increase value of year in 5 digits which is not supported and causes overflow error.
SELECT DATEADD(YEAR, 7984, ‘2015-01-31’) gives 9999-01-31 00:00:00.000 , which is the maximum value in this case.
(may be we wont live that longer :) )
For quiz 1:
Not very sure about it, but i think the SQL DateAdd function analyses the year to determine whether the year is leap or not.. meaning it is aware of leap years.
if the year in the given date satisfies the leap year condition
then end of month day is 29th else 28th.. for the month of February.
For quiz 2:
The SQL Server DateTime data type allows the storage of date
and time values between 1 January 1753 and the end of 31 December 9999.
so for the query
SELECT DATEADD (YEAR, 8000, ‘2015-01-31’);
The sum of 8000 and 2015 exceeds the specified range, which maybe causing the overflow.
1. Answer to first quiz :- February month have only 28 dayz. that’s why is show 28 for 29.30 and 31. except leap year. but 2016 is not leap year
2. Answer to second quiz :- Year can not be greater then “9999” . and you add 8000 in 2015 its greater then 9999. if year can pass the value of 9999
Hi,
The answer of first question is:
1)2015 is not a lear year.
2) we are going to add a month in January and it is february and feb max date is 28
so if date will come more than 28 after add or subtract in month then it will be 28.
Answer of second question:
we have max year in sql server is 9999 if year will come more than 9999 after adding value in current year
then it will show overflow error.
1. I did no analysis but its my guess that when adding a month in a date, it tries to return the same date with one month plus, but as the next month do have that date ( 29-feb in 2015 and 30/31 feb is also invalid) so it returns the last date of the next month. Same happens for negative additions.
2. Adding 8000 in 2015 will become 10015 which is 5 digit year and datetime supports year up to 9999.
The first one should be the expected result, since February only has 28 days, the DATEADD() function is smart enough to know that MAX(DAY) is 2015-02-28
The second is just a Y10K problem, the max date is 9999-12-31, so the largest YEAR value you can add to 2015-01-31 using DATEADD(YEAR) is 7984, any larger value will overflow the datetime column.
For Part 1
The 2015 is not leap year and hence the output.
Its trying to show the exact nearest correct output.
For 2nd part, SQL datetime only supports 4 digit years,
and hence this query works SELECT DATEADD(YEAR, 7984, ‘2015-01-31’); but adding 8000 cause overflow.
Sorry for late reply.
Thanks for good explanation!
But sir! am bigginner ! so i want set some field properrties in mysql, exa, imput mask, validation rules, Required, and index! so if am try set in my datbases am get syntax errors!
Can you help to explain the ways/ command which can help me to solve that errors
thanks!