SQL SERVER – Quiz with DATEADD Function – SQL Puzzle

February 17, 2015

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.

SQL SERVER - Quiz with DATEADD Function - SQL Puzzle puzzledatetime

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)

SQL DateTime, SQL Function, SQL Scripts, SQL Server

SQL SERVER – Schema Change Reports – SQL in Sixty Seconds #078

SQL SERVER – Trivia – Days in a Year

61 Comments. Leave new

Vijaya
February 17, 2015 7:33 am
The value is derived from the operating system. As it could not find the date of that value in the system time, it returned the last date of that month.
Reply
- Vijaya
  February 17, 2015 7:52 am
  SELECT DATEADD(YEAR, -262 ,’2015-01-31′); –Lower limit
  SELECT DATEADD(YEAR, 7984 ,’2015-01-31′);–Upper limit
  DATEADD Function works 1753 thru 9999 and handles leap year correctly.
  Reply
Kumar Harsh
February 17, 2015 9:25 am
Thanks for lovely quiz .
1) DATEADD RULE :
i) It add integer to month part of date.
ii) So datechange of next month can be maximum days of change month(previous or next month)
Thatswhy,SELECT DATEADD(MONTH, 1, ‘2015-01-30’),
Change to maximum days of next month.Since february has maximum 28 days when not leap year
So it can change maximum to ‘2015-02-28 00:00:00.000’ only.
If year was leap year it would have change to maximum to 29th
like SELECT DATEADD(MONTH, 1, ‘2016-01-30’) change to ‘2016-02-29 00:00:00.000’
iii) In short,internally it try to add 30 days to the maximum of day limit of changing month.
It stay within the changing month only.
For example SELECT DATEADD(MONTH, 1, ‘2016-01-28’)
here we are just adding one month to january,so it will be february only at any cost.
But if by adding 30 days it stay within febraruary then it will go ahead and 30 days else it will be last day of changing month.
i.e. 28th.
2) Maximum date range supported by sql server is December 31, 9999.
So 8000+2015 is more than 9999.So SELECT DATEADD(YEAR, 8000, ‘2015-01-31’);
give overflow error
Reply
RAO
February 17, 2015 10:19 am
2015 isn’t a leap year where as 2016 is a leap year.
Reply
Dhiren
February 17, 2015 10:24 am
Answers :
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 [This is a leap year]
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
2. While executing “SELECT DATEADD(YEAR, 8000, ‘2015-01-31’);” we are getting below error message because addition of 8000 year with the given date crossing the maximum valid
range define by sqlserver (9999-12-31)
“Msg 517, Level 16, State 1, Line 9
Adding a value to a ‘datetime’ column caused an overflow.”
Reply
James Ting
February 17, 2015 10:41 am
SELECT DATEADD(MONTH, 1, ‘2015-01-28’) should be Feb 28, 2015
SELECT DATEADD(MONTH, 1, ‘2015-01-29’) should be March 1, 2015
SELECT DATEADD(MONTH, 1, ‘2015-01-30’) should be March 2 2015
SELECT DATEADD(MONTH, 1, ‘2015-01-31’) should be March 3, 2015
Reply
Akshay Jain
February 17, 2015 10:51 am
Answer to Question 1: Year 2015 is not a leap year and hence february has 28 days so on adding a month to date to and after 28 will give output as 28 FEB 2015. However 2016 is a leap year and hence February has 29 days and hence when we subtract a month from march dated after 29, it will return the maximum date of month Feb 2016 which is 29-FEB
Answer to Question 2: The datatype DATETIME can have maximum year till 9999. But in query SELECT DATEADD(YEAR, 8000, ‘2015-01-31’); we are adding 8000 to 2015 which is 10015 which is causing overflow. If we replace 8000 with (9999-2015=7984) it wont throw an error. It can show maximum date till 9999-12-31.
Reply
Immu
February 17, 2015 11:04 am
Hey Dave,
to explain following query
SELECT DATEADD(MONTH, 1, ‘2015-01-28’)
As feb have 28 days above query executes sucessfully.
SELECT DATEADD(MONTH, 1, ‘2015-01-29’)
SELECT DATEADD(MONTH, 1, ‘2015-01-30’)
SELECT DATEADD(MONTH, 1, ‘2015-01-31’)
As feb have 28 days, result is rounded to feb 28
SELECT DATEADD(MONTH, 1, ‘2016-01-28’)
SELECT DATEADD(MONTH, 1, ‘2016-01-29’)
As, 2016 is leap year both the queries executes perfectly.
SELECT DATEADD(MONTH, -1, ‘2015-03-30’)
SELECT DATEADD(MONTH, -1, ‘2015-03-31’)
As feb have 28 days, result is rounded to feb 28
Reply
Karan
February 17, 2015 11:58 am
Quiz 1:
DateAdd function to add or subtract a specified time interval from a date
DateAdd with month interval will add/subtract month from specified date.
FYI: The DateAdd function won’t return an invalid date.
So when we add one month to ‘2015-01-29’, ‘2015-01-30’ and ‘2015-01-31’, DateAdd function will return ‘2015-02-28’
bcoz ‘2015-02-29’, ‘2015-02-30’ and ‘2015-02-31’ are invalid dates.
So the last day of the return month is returned.
Quiz 2:
DateAdd function support range from 0001-01-01 through 9999-12-31.
So when we execute below query
SELECT DATEADD(YEAR, 8000, ‘2015-01-31’);
it will try to add 8000 year to 2015, results 10015 year which is outside the range.
So it will throw overflow error.
Reply
MohamedFahad DasthakeerAlim
February 17, 2015 12:17 pm
1) Write the answer to first quiz – Why specific outcome when you execute queries with DateAdd and 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.
2) Write the answer to second quiz – Why specific error on the screen?
Y10k Or deca-millennium bug. Year exceeds four digits.
Reply
Marsellus
February 17, 2015 12:25 pm
Part 1:
Based on https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186819(v=sql.105) “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.” February has max 28 days (29 in leap year) so max date of given month is returned from dateadd function.
Part 2:
Based on https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186819(v=sql.105) “The return data type for a string literal is datetime.” and based on https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms187819(v=sql.105) “Date range is January 1, 1753, through December 31, 9999” which is lower than year 2015 + 8000 years and overflow occurs.
Reply
Sandeep
February 17, 2015 12:33 pm
1. 28th is the last day of february this year
2. Adding 8000 to the year will show an error as we have only year available up to 9999
Reply
Vimal AC
February 17, 2015 12:50 pm
Answer for First Question will be as below
2015-02-28
2015-02-28
2015-02-28
2015-02-28
because Feb Month end day is 28th
for second set
2016-02-28
2016-02-29
2015-02-28
2015-02-28
As 2016 is leap year.
Answer for Second Question
SQL will allow max year of 9999 so there is an overflow error.
Thanks
Vimal
Reply
Aanal Patel
February 17, 2015 1:46 pm
Explanation for Part 1:
If you use dateadd along with Month, it will always add 1 month regardless of counting days.
If you take below example:
SELECT DATEADD(MONTH, 1, ‘2016-03-31’) — It will return 2016-04-30 instead of 2016-05-01 because logically, it is adding one month to it and internally whatsoever result is, it is checking if that date is valid date or not. If no then it will take floor of nearest date.
Basically, if month + 1 is not valid date then it will try to find valid date in reverse order from the new date.
Explanation for Part 2:
It is giving error because of DATETIME maximum value.
For Datetime datatype. maximum possible value is Dec 31, 9999 and if you add 8000 + 2015 which is out of range.
If you try with SELECT DATEADD(YEAR, 8000, ‘1999-01-31’); — It will return 9999-01-31.
Reply
Tejas
February 17, 2015 2:02 pm
Part I
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’)
In these statements, SQL Sever might be trying to come up with dates > 28 for the month of February by adding exact datepart. But since 28th is the last day in February in 2015, it gives us the output of 28th Feb (last valid date) for all the statements.
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’)
Similar reasoning except, 2016 is a leap year so the last valid date for Feb in 2016 will be 29th, which we get from the above statements.
Part II
We get the error because SQL Server’s valid date range is from 1st Jan 1753 to 31st Dec 9999.
And 2015 + 8000 = 12014, which is > 9999 and hence, out of range
Reply
Arunprasath
February 17, 2015 2:20 pm
Part 1 – DateAdd – sets to the maximum day of the next month based on the leap year for February.
Part 2- The maximum value which to be passed is 7984
Reply
Chirag Bhatt
February 17, 2015 2:55 pm
Hi Pinal,
1. When using Dateadd function with month/year option,if returned month has less days in it then it returns last day of return month.
2. SQL Server – datetime data type has a limit for consisting value in it. Max. value it can store is “9999-12-31”. When we do anything beyond this limit when working with datetime type, it generates this error. It`s a highest value database engine can process.
For this example, adding 8000 years into 2015 tries to go beyond limit-“9999-12-31”. So it generates error and tells user that SQL can not handle processed value into its memory for datetime datatype.
Reply
Subir Sankar Das
February 17, 2015 3:02 pm
if you add 8000, year will be like this
“10000-01-31 00:00:00.000”, which is a limitation for a datetime datatype, year should be 4 digit only, not 5, you can add max 7984 years to your mentioned date
SELECT DATEADD(YEAR, 7984, ‘2015-01-31’);
Reply
- Subir Sankar Das
  February 17, 2015 3:03 pm
  year will be 10015
  Reply
  - Subir Sankar Das
    February 17, 2015 3:09 pm
    If you check in the advance settings in sql server “Two Digit Year Cutoff” configuration settings, it will not allow you to enter more than 9999 nos, this the cause
Deepak Ankalkoti
February 17, 2015 3:13 pm
For Part 2. Datetime SQL data type has Maximum character length of 23 only, any thing more than that cant be handled by the data type. Therefore for this current year we can add maximum of 7984 years, which make it 9999 as year. 7985 will exceed more than 9999 i.e. 1000 and now the count of total character length gets 24 therefore data type overflow error.
Reply
Srikar Reddy
February 17, 2015 3:42 pm
Hi Pinal,
I think SQL Server will consider the original calender in date calculations.
Since 2015 is a non leap year according to original calender, it gave results according to that.
And 2016 is leap year and the results are according to it.
And 8000+2015 gives 10015. Enough space may not be there to store an added digit in the date.
Reply
Fahad
February 17, 2015 5:01 pm
1. Since MONTH in DATEADD function looks for a complete month (i.e.31 days) hence if you add 1 month to 2015-01-28, output would be 2015-02-28 ( given the fact that 2015 is not a leap year and 28 is the last day of the Feb month) . This output would remain same for all first 4 selects.
However in subsequent SELECT statements below would be the results-
SELECT DATEADD(MONTH, 1, ‘2016-01-28’) ==> 2016-02-28
SELECT DATEADD(MONTH, 1, ‘2016-01-29’) ==> 2016-02-29 ( Since 2016 is leap year)
SELECT DATEADD(MONTH, -1, ‘2015-03-30’) ==> 2015-02-28
SELECT DATEADD(MONTH, -1, ‘2015-03-31’) ==> 2015-02-28
2. Max datetime value in SQL server is ‘9999-12-31’, so you can add around 7984 months to 2015-01-31. Below Query will work absolutely fine
SELECT DATEADD(YEAR, 7984, ‘2015-01-31’);
So if you add above that say 7985 it is crossing the max datetime limit causing overflow in SQL.
Reply