Interview Question of the Week #046 – How @@DATEFIRST and SET DATEFIRST Are Related?

Question: How @@DATEFIRST and SET DATEFIRST are related?

Answer: The master database’s syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as first day of the week using

SET DATEFIRST 5

This will set Friday as the first day of the week.
@@DATEFIRST returns the current value, for the session, of SET DATEFIRST.

SET LANGUAGE italian
GO
SELECT @@DATEFIRST
GO
----This will return result as 1(Monday)
SET LANGUAGE us_english
GO
SELECT @@DATEFIRST
GO
----This will return result as 7(Sunday)

In this way @@DATEFIRST and SET DATEFIRST are related. When I learned about this feature I was very glad as our company has started to server global clients and simple feature like this helps a lot to avoid confusion.

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

SQL SERVER – Puzzle with Miliseconds – Win USD 50 Amazon Gift Card

Last contest, which we had ran was in May and it had received amazing responses. I once again reached out to kind team of Embarcadero and they agreed to support the next contest. The contest has two steps and they are very simple. It took me a while to build contest, but it is a really fun one. I am very confident that once you try out the contest, you will love it.

Two Giveaways:

amazon gift cards SQL SERVER   Puzzle with Miliseconds   Win USD 50 Amazon Gift Card

(USA) USD 50 Amazon Gift Card to 1 Individual

(India) INR 2500 Amazon.in Gift Card to 1 Individual

(Rest of the world) USD 50 Amazon Gift Card to 1 Individual

Contest 1: T-SQL

Run following T-SQL script and observe the answer:

SELECT
CAST(‘2015-01-01 14:48:34.69’ AS DATETIME) FirstVal,
CAST(‘2015-01-01 14:48:34:69’ AS DATETIME) SecondVal

When we look at the answer, there is a difference between the milliseconds part in the resutlset, whereas you can notice that in the SELECT statement I have specified different milliseconds part. The question is why there is a difference in the millisecond part even though I have same value selected?

milisecond SQL SERVER   Puzzle with Miliseconds   Win USD 50 Amazon Gift Card

Contest 2: Download and Install DBArtisan

This is the easy part of the contest. You just have to download and install DBArtisan. You can download DBArtisan from here.

How to Participate?

  • Leave an answer for contest 1 in the comment section of the blog.
  • Leave a comment with the same email address which you have used to download DBArtisan.
  • The contest is open till December 1st, 2015 Midnight PST.
  • The winner will be announced on December 10th, 2015.
  • There will be three winners 1) USA 2) India 3) Rest of the World
  • Please do not forget to indicate which country do you belong to qualify for the contest.
  • All the correct answer to the contest will be hidden till the winner is announced.

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

SQL SERVER – Adding Datetime and Time Values Using Variables

Datetime datatype stores date and time part. If you have only date value, You can add time part to a datetime variable using direct literal value and + operator.

It is shown below
DECLARE @date DATETIME
SET
@date='2010-10-01'
SET @date=@date+'15:00:00'
SELECT @date AS DATETIME

The result is 2010-10-01 15:00:00.000
But it is not possible to add it using a time variable

DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+@time
SELECT @date

The error is

Msg 402, Level 16, State 1, Line 4
The data types datetime and time are incompatible in the add operator.

So the solution is to convert time datatype into datetime and add
DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+CAST(@time AS DATETIME)
SELECT @date AS DATETIME

The result is 2010-10-01 15:00:00.000

So when adding data of datetime and time datatype values, you need to be aware of this.

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

SQL SERVER – Finding If Date is LastDay of the Month

As I said many times before SQL Server does not stop amazing me continuously. It does not matter how many questions and issues I have come across so far there are always new things to learn.

Here is the question which I was asked – How do we know if any of the employees joined on the last day of the month. This was interesting for sure. It is easy to know if an employee has joined the first day of the month or not as it is always date 1. However, for any month there is no way to know the last date as it can be any of the following: 28, 29, 30, 31. The safe bet is that we add 1 to the date and check if that is 1 or not. It is quite popular and I have used that in the past.

However, from SQL Server 2012 we have a new function called EOMONTH and we can use the same to check the values as well. Here is how we can check the hire date of the employee if it is the last day or not.

SQL Server 2012 and later version: 

SELECT
[LoginID]
,[HireDate],
IIF([HireDate] = EOMONTH([HireDate]), 'LastDay', '') LastDay
FROM [AdventureWorks2014].[HumanResources].[Employee]

Earlier version of SQL Server 2012:

SELECT
[LoginID]
,[HireDate]
,CASE  DAY(DATEADD(d,1,[HireDate]))
WHEN 1 THEN 'LastDay'
ELSE ''
END 'LastDay'
FROM [AdventureWorks2014].[HumanResources].[Employee]

Both of the above queries will give following resultset:

lastday SQL SERVER   Finding If Date is LastDay of the Month

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

Interview Question of the Week #036 – What is the difference between @@DATEFIRST and SET DATEFIRST?

Question: What is the difference between @@DATEFIRST and SET DATEFIRST with respect to SQL Server?

Answer: SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as the first day of the week using

SET DATEFIRST 5

This will set Friday as the first day of the week.
@@DATEFIRST returns the current value, for the session, of SET DATEFIRST.

SET LANGUAGE italian
GO
SELECT @@DATEFIRST
GO
----This will return result as 1(Monday)
SET LANGUAGE us_english
GO
SELECT @@DATEFIRST
GO
----This will return result as 7(Sunday)

In this way @@DATEFIRST and SET DATEFIRST are related. When I learned about this feature I was very glad as our company has started to server global clients and simple feature like this helps a lot to avoid confusion.

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

SQL SERVER – Puzzle – Inside Working of Datatype smalldatetime

In the recent past, I have been bringing few puzzles around using date and time functions. All these have brought some awesome feedbacks and you all have been sharing some awesome answers. Today’s question comes from an unusual place wherein one of a developer said he was getting unexpected results with working with datetime. So here is how the conversation went:

Dev: Hi Pinal.

Pinal: Yes, buddy. How can I be of help?

Dev: I have a problem working with datetime datatype.

Pinal: Oh is it? Tell me more about it.

Dev: I am trying to CAST a string coming from a front end app to SQL Server.

Pinal: Ok, where is the problem?

Dev: Every now and then, it gives different results than what I try to send via the wire.

Pinal: I am not able to understand. Can you send me an example to understand this better?

Dev: Sure, I will send via email.

After I received the actual code and email, I resumed the conversation.

Pinal: Hey.

Dev: Yes, Sir. Did you find the reason?

Pinal: I found the reason and I am going to blog about it tomorrow. It is a simplified version of the query you sent me. So you are about to find the answers there. The hint is: you are doing a convert to smalldatetime

Dev: Well, thanks. I think I will do my homework and try to answer it in your blog too.

This concept was interesting and I planned to share this experience with you folks like a small puzzle. So what are we talking?

Look at the below query and guess what would be the output:

SELECT
CAST('2015-01-01 12:45:29.755' AS SMALLDATETIME),
CAST('2015-01-01 12:45:35.755' AS SMALLDATETIME)

This is a simple conversion but the two values are different. Why is that? Do you know the reason? Use the comments section to explain this. I may send surprise gift to 2 of the winners.

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

SQL SERVER – Puzzle with Year Function

Puzzle pieces SQL SERVER   Puzzle with Year FunctionA 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?

Please leave correct answer in comment below the blog.

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 – Quiz on knowing DATEPART and DATENAME Behaviors

quizbutton SQL SERVER   Quiz on knowing DATEPART and DATENAME BehaviorsPlaying around with date and time datatypes can be loads of fun. I hope you got the pun intended when I say it is fun. Most of the developers working with date and time datatypes feel it is a pain. I understand their sentiments, but would like to let you know that, it is not a pain as you think. If we understand how date, time and their respective functions work inside SQL Server, then most of the tasks that we complete will be a breeze. Trust me on this. I get stumped by people from time to time with these small tricks and I get into the learning curve of why that happened. It is easier than what you think.

If you did participate in my previous quiz earlier last month (SQL SERVER – Trivia – Days in a Year), then this will be really easier than you think. You will know the answer in a jiffy.

Remember the winners will get a free monthly subscription of Pluralsight. Winners will be randomly picked on March 10th midnight.

Part 1: Understanding DATENAME

The first part of this quiz is understanding what your functions are. Look at the statements below and guess what the answer would be. Then go ahead and try the same inside SQL Server Management Studio.

-- The reason is there is no “Day” specified in the source datetime.
SELECT DATENAME(DAYOFYEAR, '12:12:12.123') [DayOfYear]
SELECT DATENAME(WEEKDAY, '12:12:12.123') [WeekDay]

Should the above code work? What is the output, an error or values? If you did get an error, can you spot the reason? If you got values then can you tell why you got those values?

Part 2: Understanding DATEPART

If you got the above correct, then this should be a breeze for sure. You will get the answer easily. Let me know how many got it right just by watching the statements.

-- What about now?
SELECT DATEPART(weekday, '12:12:12.123') [WeekDay]
SELECT DATEPART(MONTH, '12:12:12.123')[MONTH]

Should the above code work? What is the output, an error or values? If you did get an error, can you spot the reason? If you got values then can you tell why you got those values? Is it different from Part 1 of your guess with DATENAME or same? Can you tell why?

If you got this far, then great. I think you got the basics covered.

Part 3: Bonus Question

I thought it would be interesting to add a twist to the tale and get your guess on what the output should be for the below queries.

-- First
SELECT DATENAME(dayofyear, '1') [DayOfYear]
-- Second
SELECT DATENAME(dayofyear, '1-1') [DayOfYear]
-- Third
SELECT DATENAME(dayofyear, '2:2') [DayOfYear]
-- Fourth
SELECT DATENAME(dayofyear, '23:25') [DayOfYear]
-- Fifth
SELECT DATENAME(dayofyear, '24:25') [DayOfYear]

Now which of the above queries will run without any error?

Let me know via comments to all your guesses and the reasons for those guesses. Based on the response, I will look at giving away some goodie to one lucky winner.

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 – Interesting Function AGENT_DATETIME

One in a while I come across such interesting side of SQL Server that it even surprises me. Just the other day I came across function AGENT_DATETIME. I had never used it before and now I just found usage example of the same.

There can be several methods to convert integer values to a datetime value. But did you know that there is a system function in MSDB database that does this job? It is AGENT_DATETIME function. It accepts two parameters one is date value in YYYYMMDD format and another is time value in HHMMSS format

SELECT MSDB.DBO.AGENT_DATETIME(20150119,0)

The result is

2015-01-19 00:00:00.000

SELECT MSDB.DBO.AGENT_DATETIME(20150119,171911)

The result is

2015-01-19 17:19:11.000

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