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.

In this article, we will dive into the concepts of date and time datatypes in SQL Server and explore some of the commonly used functions.

Part 1: Understanding DATENAME

The first part of our exploration involves understanding the DATENAME function. Let’s take a look at the following code:

-- 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 got an error, can you spot the reason? If you got values, can you tell why you got those values?

Part 2: Understanding DATEPART

If you got the above correct, then this part should be a breeze for you. Let’s take a look at the following code:

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 got an error, can you spot the reason? If you got values, can you tell why you got those values? Is it different from Part 1 of your guess with DATENAME or the same? Can you tell why?

Part 3: Bonus Question

Now, let’s add a twist to the tale and get your guess on what the output should be for the below queries:

SELECT DATENAME ( dayofyear , '1' ) [DayOfYear]
SELECT DATENAME ( dayofyear , '1-1' ) [DayOfYear]
SELECT DATENAME ( dayofyear , '2:2' ) [DayOfYear]
SELECT DATENAME ( dayofyear , '23:25' ) [DayOfYear]
SELECT DATENAME ( dayofyear , '24:25' ) [DayOfYear]

Which of the above queries will run without any error? Let me know your guesses and the reasons for those guesses in the comments section below. Based on the response, I will look at giving away some goodies to one lucky winner.

Stay tuned for the next part of our exploration where we will delve deeper into the world of date and time datatypes in SQL Server.