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)
13 Comments. Leave new
Without trying part three, I am guessing all four will return the default of ‘1900-01-01’
out put is 1.converting date and/or time(12:12:12.123)
No guesses !!
for Part 1: Understanding
In the DATENAME function Date parameter expecting a validate date but in the example you are passing value in time format. If date is not in valid then function will use the default date as 01/01/1900 so that is the reason if we run the function it is returning
1 for SELECT DATENAME(DAYOFYEAR, ’12:12:12.123′) [DayOfYear]
Monday for SELECT DATENAME(WEEKDAY, ’12:12:12.123′) [WeekDay]
for Part 2: Understanding DATEPART
Reason is same. in the DATEPART date parameter expect a validate date if we pass any other value other than date then it will assign the default value as 01/01/1900 and on the basis of this we are getting following results
1) first query SELECT DATEPART(weekday, ’12:12:12.123′) [WeekDay] returning 2 because on 01/01/1900 date Weekday is Monday and which is on 2nd Position
2) Second query will return 1 because on the default date we January as first month so returning 1
Part 3: Bonus Question
in simple if we dont pass the validate date and pass the ONLY TIME it will set the default value to 01/01/1900 so for all the query will have date as 01/01/1900 and return the result on the basis of this date.
in the last query set
#1 will not run because 1 is not properly formatted as Date or Time
#2 will not run because 1-1 will not perform any arithematic operation and not have a valid date or time
#3 will run properly and will return 1 because it is first day of Year and date 01/01/1900 is default
#4 will run properly and will return 1 because of first day of year and default date that will club with time is 01/01/1900
#5 will not run because 24:25 is not a valid time.
For Part1&2:
DATENAME() and DATEPART() functions take default date value as 1753-01-01 which is minimum date value for a datetime in SQL Server i.e. SELECT DATENAME(DAYOFYEAR, ‘1753-01-01 12:12:12.123’) [DayOfYear] and executes seccessfully.
For Part3:
only third and fourth query will execute successfully.
SELECT DATENAME(dayofyear, ‘2:2′) [DayOfYear]
output: 1
SELECT DATENAME(dayofyear, ’23:25’) [DayOfYear]
output: 1
With Regards,
Mithun Kumar
Part1 & Part2:
As yyyy:mm:dd value is not supplied in date value, both functions are taking default value as ‘1753-01-01 12:12:12.123’. This date part excluding the time is the minimum date value for a datetime in SQL Server.
Part3:
Only third and fourth query will work and each will give output as 1.
third one
Part 1:
DATENAME ( datepart , date ) : Returns a character string that represents the specified datepart of the specified date.
If the data type of the date argument does not have the specified datepart,
the default for that datepart will be returned only when a literal is specified for date.
For example, the default year-month-day for any date data type is 1900-01-01.
The following statement has date part arguments for datepart,
a time argument for date, and returns 1, Monday.
SELECT DATENAME(dayofyear, ’12:10:30.123′)
,DATENAME(weekday, ’12:10:30.123′);
Part 2:
DATEPART ( datepart , date ) : Returns an integer that represents the specified datepart of the specified date.
If the data type of the date argument does not have the specified datepart,
the default for that datepart will be returned only when a literal is specified for date.
For example, the default year-month-day for any date data type is 1900-01-01.
The following statement has date part arguments for datepart,
a time argument for date, and returns 1, 2.
SELECT DATEPART(month, ’12:10:30.123′)
,DATEPART(weekday, ’12:10:30.123′);
Part 3:
None of the query will run successfully since here is the invalid datepart
For Part1: Got o/p.
Output is based on SQL default date 1 January 1753.
So DAYOFYEAR = 1 and Monday is the day on 1 January 1753.
For Part 2:
[WeekDay] – 2 ( as week starts on Monday)
[MONTH] – 1 ( with default date)
For Part3:
First, Second does not work because, its invalid date format.
— Third
SELECT DATENAME(dayofyear, ‘2:2′) [DayOfYear] — works, valid time with default date
— Fourth
SELECT DATENAME(dayofyear, ’23:25’) [DayOfYear] — works, valid time with default date
Fifth fails, as time in invalid ( more than 24 ?)
Part 1
This runs without error. Result:
1
Monday
De default date January 1, 1900 is used.
This is the first day of the year and a monday.
Part 2
This runs without error. Result:
2
1
De default date January 1, 1900 is used.
Monday is the second day of the week.
January is the first month.
The result is different from part 1 because a different function is called.
Part 3:
Only these two run
SELECT DATENAME(dayofyear, ‘2:2′) [DayOfYear]
SELECT DATENAME(dayofyear, ’23:25’) [DayOfYear]
These are valid time values.
De default date January 1, 1900 is used.
This is the first day of the year
The other queries don’t have valid times or dates.
I recognized right away that they were time formats not date. However I had no Idea what would be considered default nor what would be returned.
However, it gave me something to learn and a place to start.