SQL SERVER – Quiz with DATEADD Function

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 that I would love to hear your views on what the output would be:

Quick Trivia

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 (http://blog.sqlauthority.com)

SQL SERVER – Identify and Filter In-Memory Optimized Tables – SQL in Sixty Seconds #079

Earlier I had written a blog about SQL SERVER – Beginning In-Memory OLTP with Sample Example which covers the basics of working with In-Memory OLTP. Though that post gets you started, one of my colleague asked me if there was an easier way to identify In-Memory Tables when working with SQL Server Management Studio. As a follow up I wrote another blog post over here where I demonstrate the same with images and query over here: SQL SERVER – Filter In-Memory OLTP Tables in SSMS.

I have converted the same blog post in SQL in Sixty Seconds video over here.

Let me know your opinion about it.

Here are few other blog posts related to this concept, which I have written earlier.

Reference: Pinal Dave (http://blog.sqlauthority.com)

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

Earlier, I wrote a blog post about Schema Change Reports and Finding Tables Created Last Week – DBA Tip. I received quite a few emails about the same. It seems like a quite a popular topic. As per few requests I have created a very short 60 second video on this subject.

Let me know your opinion about it.

Here are few other blog posts related to this concepts, which I have written earlier.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Puzzle – Why Divide by Zero Error

Earlier I asked a puzzle SQL SERVER – Puzzle – Why Decimal is Rounded Up? and it was very well received by all of you. You can read various comments posted in the blog post and they are indeed very enlightening. After posting that blog post, I received quite a lots of emails asking for more puzzle similar to that. Based on your request, here is another puzzle which is very similar to the earlier puzzle but have a very different approach.

The question is why following T-SQL gives a famous divide by zero error.

SELECT 10000/(17/17/17) AS Result2;

When we execute above query it gives following error:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Here is the screenshot of the error.

Hint: Read the comments in this blog post.

I will be announcing the winner of this puzzle in my newsletter. There will be a surprise gift of USD 29. Leave your answers in the comment.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Puzzle – Why Decimal is Rounded Up?

Lots of people know the answer to this puzzle, but I am sure there are many who do not know the answer of the same as well. Here is the puzzle again for those who have vacation for the Holidays!

Here is the first script:

-- Decimal is rounded up
SET @MyValue = 10000/17
SELECT @MyValue DecResult

Here is the second script:

-- Decimal is not rounded up
SET @MyValue = 10000/17.0000
SELECT @MyValue DecResult

Here is the question – When we execute the first script the decimal’s are rounded up and when we execute the second script the decimal’s are not rounded up. WHY?

Just leave your answer in the comments field. I will select two random winners in the first week of January and will send surprise gift worth USD 29.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fixing Backup Error – Operating system error 5(Access is denied.) – SQL in Sixty Seconds #077

This error has to be one of the most famous error. I have installed SQL Server quite a many times, but I keep on getting this error once in a while. This is also one of the most searched error online. I have previously blogged about this but today, I planned to create a small video on the same subject. In this video I demonstrate how one can resolve the permissions issue with this error.

ere are few other blog posts related to this error, which I have written earlier.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Take the Quiz for a chance to win a Quadcopter Drone

It has been a long time since we ran quiz. So let us get ready for a quiz. The quiz has two parts. You have to get both the parts correct to win Quadcopter with Camera (we will call it drone). We will be giving away a total of 2 Quadcopters.

The quiz is extremely easy and I will ship the Drone anywhere in the world where Amazon will ship it.

Let us jump directly to the quiz. Please complete both the parts the contest. 

Contest Part 1: Brain Teasers

Please execute following script and answer the questions.

-- What will be the output of the following? and Why?
-- What will be the output of the following? and Why?
-- What will be the output of the following? and Why?

Contest Part 2: Download and Activate Rapid SQL

Question: Download and Activate Rapid SQL.

Hint: You have to download and activate Rapid SQL. If you do not activate Rapid SQL, you will be disqualified for the contest. Why take risk, let us start!

That’s it!

Just answer above questions in the following comments area, in following format.


  • Download RapidSQL from this link.
  • Use comments area right below the blog to take participation in the contest
  • Answer before November 21, 2014 midnight GMT.
  • The winner will be announced on December 8.
  • The winner will be selected randomly from all the valid answers.
  • All the valid answers will be kept hidden till December 24, 2014.
  • There will be a total of two winners.
  • The contest is open for any country of the world where Amazon ships products.

Reference: Pinal Dave (http://blog.sqlauthority.com)