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
1) Answer to first quiz – It considers end of month in order to return the result. Before that it also checks for the validation of input dates.
Since the Output date is MONTH perspective it gives the same result
2) Answer to second quiz – Since it exceeds the range (January 1, 1753, through December 31, 9999) it throws an overflow error.
Hi Pinal,
Here are the answers:
For part 1 :
SELECT DATEADD(MONTH, 1, ‘2015-01-28’)
— 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2015-01-29’)
— 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2015-01-30’)
— 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2015-01-31’)
— 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2016-01-28’)
— 2016-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2016-01-29’)
— 2016-02-29 00:00:00.000
SELECT DATEADD(MONTH, -1, ‘2015-03-30’)
— 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, -1, ‘2015-03-31’)
— 2015-02-28 00:00:00.000
Reason : If the date day does not exist in the return month, the last day of the return month is returned. In this case, the date month has less days than the return month and the date day does not exist in the return month.
For Part 2 :
Due to the data type limitation, the date argument cannot be incremented to a value outside the range of its data type.
Action done on months and not days
First set of queries in Part 1 will return same output: 2015-02-28, while next set of queries will return ‘2016-02-28’ for first query and rest all will return same output: 2016-02-29.
Syntax for dateadd function: DATEADD (datepart , number , date )
Date data type ranges for each datepart:
YYYY is four digits from 1753 through 9999 that represent a year.
MM is two digits, ranging from 01 to 12, that represent a month in the specified year.
DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.
If datepart is month and the date month has more days than the return month and the date day does not exist in the return month, the last day of the return month is returned.
Since 2015 isn’t leap year, adding a month in first set of queries returns last day of the month which 28th Feb as per the DD range in Date Datatype.
Similarly 2016 being leap year, second set of queries returns last day i.e, 29th Feb 2016 except the first query which returns 28th Feb 2016.
Part 2:
As mentioned above year range in date data type is from 1753 through 9999.
Therefore in below mentioned query,the number value(8000) that is added to the datepart(Year) value exceeds the range of the date data type. The following error message is returned: “Msg 517, Level 16, State 1, Line 1 Adding a value to a ‘datetime’ column caused overflow.”
SELECT DATEADD(YEAR, 8000, ‘2015-01-31’)
part 1: should give 2015-2-28 to all of them, it will add a month, but its not going to overrun the number of days in the month. Part 2: it should throw the overflow error, because datetime only runs to 9999-12-31 and adding 8000 puts us over that number
QUIZ 1:
Any DateAdd month that has fewer than the number of current days returns the max day for the next month as the day portion.
QUIZ 2:
Any DateAdd year that returns a date greater than 12/31/9999 will return an error. Only 4 digit years are allowed currently.
For part-1, since the input date month has more days than the return result date, the last day of the return date is returned. Thus 28-02-2015 is returned for the following :
SELECT DATEADD(MONTH, 1, ‘2015-01-29’)
SELECT DATEADD(MONTH, 1, ‘2015-01-30’)
SELECT DATEADD(MONTH, 1, ‘2015-01-31’)
For part-2, the query output is out of the range of the datetime data type (the upper range for which is ‘9999-12-31 23:59:59.998′.)
because 2015 is not a leap year. In short SQL server also check the leap year.
I also try to change the year to 2013 and 2012 to test and the output is the same.
Is this valid ? :)
Part 2: Max year is 9999
Thus if we add 7985 and above this will cause an error because the increment value + Year today is more than 9999 (Year Today + 8000) = 10015 that’s why we are encountering an error.
SELECT DATEADD(YEAR, 7984, ‘2015-01-31’); this will work.
Hi Pinal,
1. The input date (days only), if it’s greater than or equal on the output date in (days only). It will return the whole month of the output date.
a. For example DATEADD(MONTH, 1, ‘2015-01-31’) will return ‘2014-02-28’ because the input date has 31 days and output has only 28 whole days. Thus, returns the whole month.
2. The max date in SQL Server is only 9999-12-31. In DATEADD(YEAR, 8000, ‘2015-01-31’), adding it would be 10015. Also, try this SELECT DATEADD(MONTH, 1, ‘9999-12-30’) and will produce the same error.
Thanks!
1.The function is for month add and it’s change to february, in the day part it’s because february has 28 days.
2. Maximum range of datetime is December 31, 9999
The first part is simple, adding or subtracting months to a date where the day is > 28 to a month that has only 28 days forces sql to round down the day part. I think the easiest way to understand this is like order of operations. When working with months the month part is handled first and after that the day is made to fit.
The second part is even more simple, as noted in the documentation the highest year that can by fit in a datetime is 9999 so adding 8000 to 2015 would be 10015. I would personally prefer to see and “out of range value” error for this instead of the “overflow” but a date is internally handled as a single value so it does make sense this way.
1)
SELECT DATEADD(MONTH, 1, ‘2016-01-28’) Answer :- 2016-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2016-01-29’) Answer :- 2016-02-29 00:00:00.000
SELECT DATEADD(MONTH, -1, ‘2015-03-30’) Answer :- 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, -1, ‘2015-03-31’) Answer :- 2015-02-28 00:00:00.000
Reason :- when we are adding month with one value in any date. It will be add one month on that date. But if that date is not available then it will take last date of added month.
Same way it’s work for the negative value. for third query we are passing -1 so it would show us February month and 30 date , but in February 2015 last date is 28th.
so it’s showing us ‘2015-02-28 00:00:00.000’ as result.
2)
SELECT DATEADD(YEAR, 8000, ‘2015-01-31’);
Now when we are executing above query it will be give overflow error.
Because in our date year is 2015 and we are try to add 8000 value as year in that date. so counting is as below.
2015 + 8000 = 10015
But SQL support year up to 9999. so for year 10015 it is give us error.
Hi Pinal,
Part 1 Answer-
Generally this function returns the same date for next month(because of parameter value 1) and if day value is greater than last calendar date then function returns last calendar date of month.
In Sql server there is range_value specified for each month.
so if given_value > range_value
then return last day of month.
Part 2 Answer-
According to MSDN:
The date argument cannot be incremented to a value outside the range of its data type.
In the above statement, the number value that is added to the date value exceeds the range of the date data type.
The following error message is returned: “Adding a value to a ‘datetime’ column caused overflow.”
Thanks,
Vivek Chandel
FOR PART 1: WE ARE ADDING MONTH INSTEAD OF DAYS.
IF WE ADDED DAYS IT SHOULD BE WORKING FINE
SELECT DATEADD(DD, 30, ‘2015-01-31’)
SYNTAX IS CHECKING ONLY TO ADD 1 MONTH AND NOT VALIDATING DATES, HENCE SHOWING LAST DATE OF MONTH
FOR PART 2: YEAR DATATYPE IS DEFINED FOR 4 CHARACTERS BUT IN OUR SITUATION, IT IS 5 HENCE THROWING ERROR.
ad. 1 February is not “normal” month. Every 4 years when leap-year is, February have 29 days, in normal years it have 28. In this particular examples SQL Server tries to change month and best fit a day at end of this month. There is no possibility to find 29-31 day in February so SQL takes last day of February which is 28 in 2015.
2016 it is leap-year where February have 29 days, thats why SELECT DATEADD(MONTH, 1, ‘2016-01-29’) returns 2016-02-29.
ad.2 max year in SQL Server is 9999. 2016+8000 is more than 9999.
1) When we add month part of date add function , date is ‘2015-01-29’ we are adding one month , the blind result is
‘2015-02-29’ but if the day is not there it returns to the lastday of the month, same thing happen in ‘2015-01-30’
and ‘2015-01-31’.
Answer to Part1: Since january month has more number of days(31st end of month) than february(28th last day) so last day of month 28-FEB-2015 is returned from all 4 queries
hi Pinal,
Part 1:
SELECT DATEADD(MONTH, 1, ‘2015-01-28’) — 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2015-01-29’) — 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2015-01-30’) — 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2015-01-31’) — 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2016-01-28’) — 2016-02-28 00:00:00.000
SELECT DATEADD(MONTH, 1, ‘2016-01-29’) — 2016-02-29 00:00:00.000
SELECT DATEADD(MONTH, -1, ‘2015-03-30’) — 2015-02-28 00:00:00.000
SELECT DATEADD(MONTH, -1, ‘2015-03-31’) — 2015-02-28 00:00:00.000
While adding given MONTH interval to the specified date, it returns END of MONTH as output if it exceeding to next month..
Here, DATEADD(MONTH, 1, ‘2015-01-29’) — adding 1 month to January 2015, So it should be FEB 29th 2015. But 2015 is not leap year, so it truncates to the END OF MONTH ( i.e. 2015-02-28)
DATEADD(MONTH, 1, ‘2016-01-29’) — 2016-02-29 00:00:00.000
Here, 2016 is leap year. So the 2016-02-29 is valid date, hence it returns 2016-02-29 as output
DATEADD(MONTH, -1, ‘2015-03-31’) — 2015-02-28 00:00:00.000
Here, it substracts 1 MONTH from March…. So its 31st FEB, 2015. This is invalid date. So it truncates date to END OF MONTH as 2015-02-28
NOTE: After adding interval to datepart, if the resulted date is invalid then it returns END OF MONTH as output date…..
Part 2:
SELECT DATEADD(YEAR, 8000, ‘2015-01-31’); — retruns error
The reason behind this, the resulted YEAR becomes 10015 ( its 5 digit number) and also it is exceeding maximum length/value of YEAR part ( 9999)….
SELECT DATEADD(YEAR, 7984, ‘2015-01-31’); — 9999-01-31 00:00:00.000
Here, the resultant year part is 9999. If you add more than this inteval( 7984) part to the year 2015, it returns ERROR as it is exceeding maximum/out boundary value of YEAR part….
Because it will cause the year to become 5 digit where as the existing system is built to have 4 digits for a year.
Command like below is acceptable as it will cause the year to be 9999
SELECT DATEADD(YEAR, 7984, ‘2015-01-31’);
where as
SELECT DATEADD(YEAR, 7985, ‘2015-01-31’);
will cause the year to be 10000 which is an overflow for a space of 4 digits.