# SQL SERVER – Puzzle with Year Function

A couple of weeks back, I ran a contest with MONTH () function and had close to 300 of you answer the puzzle. It was a great brain teaser and we had an awesome response from you. Most of you got the answer right and it is great to see you folks getting the basics right.

When I was on that step, I was wondering to run this second contest. Sometimes our basics can get tricked if we add a twist to the whole setup. Below are two sets of Queries and I would want you to guess what is the output and why?

### Query 1:

What is the output for the below query? And why?

```DECLARE @dt DATE = '0001-1-1' SELECT MONTH(@dt), YEAR(@dt)```

Part of the answer is in the earlier puzzle I mentioned starting this blog. But what would the year value show? Take a guess and let me know.

### Query 2:

I have made a small change in the default value for the second query. Now can you guess what the output from this query is?

```DECLARE @dt DATE = '1-1-1' SELECT MONTH(@dt), YEAR(@dt)```

As part of the hint I can tell you the MONTH function returns the same value. But there is something different for the YEAR function.

So here is the quiz – Why are the values different? What is the reason behind this?

I will announce winner of this contest in 48 hours. Two people who give the right answer, I will share a learning resource worth USD 29 on June 11 via Email.

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

# SQL SERVER – Trivia – Days in a Year

Every time I wrote a blog, I tend to get back to most of them via the search. Most of you ask me questions and I do few simple search back to make sure I am able to address them. Yesterday as I was searching for an interesting question, Now back to the question that got me there, one of a friend said he was asked in an interview on how to efficiently find the number of days in a given year.

The question was interesting but what baffled me was – do people still ask such questions? It was a challenge that I wanted to share with you. I wanted to take the challenge and immediately got to my SQL Server machine to solve. The instinctive behavior was to use some basic methods that involved DATEADD and DATEPART options.

The first solution that I got was:

```--This method will work on SQL server 2005 and above. DECLARE @year AS INT SET @year=2012 SELECT DATEPART(dy,(DATEADD(YEAR,@year-1899,0)-1)) AS [TOTAL NO OF DAYS] GO ```

Immediately I sent the above option. Then he quizzed me asking, can you use some of the New Date time functions that were introduced with SQL Server 2012. Now, I was not prepared for the googly that came my way. But after getting my heads around it, I came up with the below query.

```--This technique will work in SQL Server 2012 and above. DECLARE @year AS INT SET @year=2012 SELECT DATEPART(dy,DATEFROMPARTS(@Year,12,31)) AS [TOTAL NO OF DAYS] ```

Woot !!! That was not simple as I had to search my blog for ideas.

Quiz: Can you come up with some solution which will have lesser number of functions involved? Can you use some of the new SQL Server 2012 Date functions in your solution? Let me know via comments.

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

# 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.

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.

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.

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 DECLARE @MyValue DECIMAL(10,2) SET @MyValue = 10000/17 SELECT @MyValue DecResult GO ```

Here is the second script:

```-- Decimal is not rounded up DECLARE @MyValue DECIMAL(10,2) SET @MyValue = 10000/17.0000 SELECT @MyValue DecResult GO```

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

```-- What will be the output of the following? and Why? SELECT 28E3 -- What will be the output of the following? and Why? SELECT 28F3 -- What will be the output of the following? and Why? SELECT F328 ```

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.

### Remember:

• 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)

# SQL SERVER – Puzzle – ISNUMERIC and Unexpected Results – SQL in Sixty Seconds #076

It has been a long time since I have asked, puzzled on this blog so let us have fun time together with ISNUMERIC function. If you get the correct answer to this question, I will give you one month free subscription to Pluralsight.

The question is in the form of video which is displayed here. Watch the video and answer correct question in the comment on the this blog. You have 48 hours from the time of publishing this blog to answer it correctly.

If you due to any reason, you can’t watch the video, here is the image taken from the video. I strongly suggest you watch this video to get a clear idea of the puzzle.

In the above image, when I execute first select statement, it returns me answer as 0 but when I execute the second function, it gives me answer as 1. What is passed in the second select statement in ISNUMERIC function which is producing answer as 1, even though it is not visible.