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