The DATENAME function is a system function in Microsoft SQL Server we can use to present meaningful date information to the user. We use this function to basically spell out a specific part of a date value.
For example, if we have a date of 4/1/2021, we can use DATENAME to give us the character string representation of the actual name of the month (“April“), or what day of the week April 1st was (it was a “Thursday“). Presenting this information to the user can be extremely helpful in many situations.
In this tutorial, we will learn all about the handy DATENAME function. We will discuss these topics:
- The syntax of the DATENAME function
- The “datepart” values we can use in DATENAME
- Examples of using DATENAME
- Tips and tricks
Let’s do it.
1. The syntax of the DATENAME function
The syntax is very easy:
DATENAME(<datepart>,<date value>)
The <datepart> will be a specific code, representing what part of the date you want to get the name of. For example, if you want the name of the month, you would use a <datepart> value of month.
You could also use a <datepart> value of day, weekday, year, or many others.
We’ll look at the complete list of <datepart> values available to us in the next section.
The second argument is the <date value>. This is the actual date from which you want to get a part. This can be one of several things:
- String literal, like ‘2021-04-15’
- Column name, like OrderDate
- Variable name, like @newShipDate
- Expression, like GETDATE()
Again, we’ll look at some examples soon, but here is a quick example to give you the idea:
So for <datepart>, I chose to see the MONTH of the date in question. Speaking of the “date in question“, I chose to pass a string literal as my <date value>.
The result of DATENAME, therefore, is the name of the month of the date ‘4/15/2021‘, which is of course April.
2. The “datepart” values we can use in DATENAME
There are several <datepart> values you can use as your first argument to DATENAME. Here is the full list, along with their abbreviations you could choose to use instead, pulled directly from the Microsoft documentation:
Datepart values – Abbreviations:
MONTH - MM or M DAY - DD or D YEAR - YYYY or YY DAYOFYEAR - DY or Y QUARTER - QQ or Q WEEKDAY - DW or W HOUR - HH MINUTE - MI or N SECOND - SS or S MILLISECOND - MS MICROSECOND - MCS NANOSECOND - NS
Let’s talk about the abbreviations for a second. Let’s revisit this example:
If we wanted to, we could have abbreviated the word MONTH to just MM, like this:
Notice the result is the same.
We could abbreviate it even further by just using the letter M, like this:
Again, notice the result is the same.
The choice is yours. Personally, I like to be very explicit with my code, so I would probably choose to use the whole word and not an abbreviation. This is especially true when we think about some of the abbreviates that might not be so obvious, like the abbreviation for MINUTE which can be just the letter N. If we saw that, we would probably need to Google it to figure out what that means!
3. Examples of using DATENAME
We understand there are 4 different ways you can outline the <date value> of the DATENAME function. Here they are again:
- String literal, like ‘2021-04-15’
- Column name, like OrderDate
- Variable name, like @newShipDate
- Expression, like GETDATE()
Let’s look at an example of each:
String Literal
We’ve already seen a couple examples of a string literal as the <date value>, but let’s look at another one:
You might be wondering something: “Why isn’t the resulting value the spelled-out string ‘Fourteen‘, or maybe ‘Fourteenth‘?”
The point is that the resulting value is a character string data type. Specifically, the resulting value will be an NVARCHAR data type.
(If you need to understand a thing or two about character string data types, check out this tutorial)
When it comes to numeric values (like DAY, HOUR, or SECOND), the DATENAME function will still give you a character string representation of the number.
Let’s prove it. There is a handy system function called SQL_VARIANT_PROPERTY we can use to tell us the data type of a variable:
So understand even though the output of DATENAME is “14“, it is the character string representation of the number 14, and NOT the integer 14.
Get it?
Column Name
Let’s say we have an Orders table that keeps track of all the orders that have been placed for our business. This table has an OrderDate column. Here is the data in that table:
In this table, we can see the ID of each order, the ID of the customer who placed the order, the ID of the product they bought, and how many of said product they bought.
But let’s focus on the OrderDate column. Let’s see what day of the week each order was made:
SQL will look at each OrderDate value for each row and give us the day of the week it’s on. Nice.
Let’s think about a real-world example of how this might be useful. Let’s use a GROUP BY clause to count the number of orders that have been placed on a given day:
So it looks like most of our past orders were placed on a Saturday or a Wednesday. This is great information to have. We can infer from this data that maybe people are in a shopping mood on Saturdays and Wednesdays. We might choose to send special offers to our customers on Saturdays and Wednesdays when they are more likely to buy.
Folks, that’s called Data Analysis. Amazon and Facebook have mastered it.
Variable Name
This one is fairly straightforward. We can create a variable to hold a date value, then pass that variable to DATENAME to get the string representation of the part we want.
Like this:
Expression
Likewise, we could have skipped the whole process of creating and passing a variable and just used GETDATE() directly in our DATENAME function, like this:
Easy peasy.
4. Tips and tricks
Here is a list of tips and tricks you should know when using the DATENAME system function:
Tip # 1: You should understand what happens when your datepart doesn’t exist in the date value.
Take this example: SELECT DATENAME(SECOND, '6/15/2021') AS 'Value'
Or this example: SELECT DATENAME(DAY,'09:34:57') AS 'Value'
If the <date value> is a character string, the result of DATENAME will be the default value for the given <datepart>. For example, the default value of the SECOND datepart is 0. The default value of the DAY datepart is 1.
So let’s run those queries to see what we get:
But what if our <date value> is NOT a string literal?
If your <date value> is a variable or column whose data type doesn’t support the <datepart> you’re looking for, you will get an error message along the lines of “The datepart is not supported by date function datename for data type“. Take a look:
The TIME data type doesn’t support a DAY datepart. Why would it?
Tip # 2: The data type returned by DATENAME is NVARCHAR
Remember, the character string returned by DATENAME is an NVARCHAR data type. You can learn more about this data type and all of the other character string data types in this tutorial: SQL Server character data types (and the differences between them)
Next Steps:
Leave a comment if you found this tutorial helpful!
I’ve mentioned a couple times the importance of knowing the differences between all the character string data types we have available to us in Microsoft SQL Server. Take a look at the full tutorial to learn more:
SQL Server character data types (and the differences between them)
While we’re on the topic of data types, you may find yourself in a situation where you need to convert a value from one data type to another on the fly. We use the CAST and CONVERT functions to do just that. Make sure you understand those system functions and how they can help us:
CAST and CONVERT: A How-To Guide
Finally, I created a FREE Ebook you should get your hands on:
FREE EBook on SQL Server Data Types!
This guide provides a very simple rundown on several useful data types used in Microsoft SQL Server. Definitely download it today!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
