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.

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

Please execute following script and answer the questions.

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

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.

Remember:

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

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.

Please leave your answer as a comment to this blog post.

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

SQL SERVER – A Question on SSMS Prompt about Connect to Server

Today is Sunday, so letting us have some fun thing to discuss and think about. If you have not read my earlier blog post SQL SERVER – Connecting to Azure Storage with SSMS. I have discussed about how to connect to the SQL Azure storage via SSMS. Here is a follow up question I have on the same subject.

Question: When we connect to object explorer via the main toolbar menu, we get a prompt where we can connect to Azure Storage but when we open first object explorer why do we see the option to Azure Storage.

To better explain my question, please see the image attached herewith.

Do you have any answer for the same? If yes, please share.

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