DATENAME Function of SQL Server

In this SQL Server tutorial, you will learn about the DATENAME function in SQL Server.

You will understand the syntax of the DATENAME() and extract the different date parts. Also, you will learn how to use the DATENAME() column with the table.

DATENAME Function of SQL Server

The DATENAME() function in SQL Server retrieves the specific part of the given date. It extracts part of the date, such as the day, month, year, weekday, or quarter of that date.

It is helpful when you need to categorize or filter data based on a specified date part and in data analysis or reporting.

The syntax is given below.

DATENAME(date_part, date | datetime);

Where,

• DATENAME(): The function extracts the date part of the specified date.
• date_part: This is the date part you want to extract from the given date.
• date | datetime: This is the date or datetime from which the date or time part is extracted.

You can use the following date parts in the DATENAME() function.

The return type of the DATENAME() function is nvarchar. Now, let’s take a simple example: extract the date part of the date.

Suppose you want to extract the year part of the ‘2023-03-11’. For that, use the below query.

SELECT DATENAME(yy, '2023-03-11') AS YearOfDate;

Or

SELECT DATENAME(yyyy, '2023-03-11') AS YearOfDate;

The DATENAME() function in the above query returns the year of the ‘2023-03-11’ date as 2023. Here, two abbreviations as the date part for the year are provided. The first is the yy, and the second is yyyy.

Both kinds of year date parts return the same year value from the date, depending on which one you want to use.

Let’s take another example, where you will extract the month’s name of the date. For example, you have a date ‘2023-03-11‘; to extract the month part of this date, query the below query.

SELECT DATENAME(m, '2023-03-11') AS MonthNameOfDate;

--OR

SELECT DATENAME(mm, '2023-03-11') AS MonthNameOfDate;

This time, the DATENAME() function in the above query returns the month name of the ‘2023-03-11’ date as March. Here, two abbreviations, as the date part for the month name, are provided. The first is the m, and the second is mm.

In the same way, you can use the different date parts specified in the above table with the DATENAME() function to retrieve the specific part of the date.

DATENAME Function of SQL Server with DATETIME Value

The DATENAME() function also extracts the time part of the given datetime value (which contains both date and time).

For example, using the query below, let’s extract the hour from the current date and time.

SELECT DATENAME(hh, CURRENT_TIMESTAMP) AS HourOfDateTime;

The hour of the current date and time is 11, as you can see in the above output of the query.

Understand the query part DATENAME(hh, CURRENT_TIMESTAMP),

• The CURRENT_TIMESTAMP function is passed to the DATENAME() function as the date.
• The CURRENT_TIMESTAMP function retrieves the current date and time of the system.
• The date part ‘hh’ is passed to the DATENAME() function, representing the hour.

Suppose the function CURRENT_TIMESTAMP returns the current date/time as ‘2023-11-29 11:04:00.663’. Then the hour part will be as 11.

You can also extract the minute part of that datetime and use the below query.

SELECT DATENAME(mi, '2023-11-29 11:04:00.663') AS MinuteOfDateTime;

--OR

SELECT DATENAME(n, '2023-11-29 11:04:00.663') AS MinuteOfDateTime;

After executing the above query, the DATENAME() function returns the minute of the given datetime value ‘2023-11-29 11:04:00.663’ as 4.

Again, here are two abbreviations for the minute, mi, and n in the DATENAME() function. Next, extract the second part of the given datetime value. For example, you have a datetime value of ‘2024-11-29 12:04:30.653’.

SELECT DATENAME(s, '2024-11-29 12:04:30.653') AS SecondOfDateTime;

--OR

SELECT DATENAME(ss, '2024-11-29 12:04:30.653') AS SecondOfDateTime;

When you provide the date part as s and ss to the DATENAME() function with a datetime value as ‘2024-11-29 12:04:30.653’, it returns the second part of that datetime value as 30.

Take one more example, and this time, extract the milliseconds of the given datetime value using the below query.

SELECT DATENAME(ms, '2024-11-29 12:04:30.653') AS MilliSecondOfDateTime;

When you pass the date part as ms, the above query returns the millisecond of the given datetime value ‘2024-11-29 12:04:30.653’ as 653.

This is how to extract the time part of the given datetime value using the DATENAME() function in SQL Server.

DATENAME Function of SQL Server with Table

In this section, let’s see how to use the DATENAME() function with the table to find the specific date part from the column that contains the datetime value.

An Events table with EventID, EventName, EventDate, and TimeZone is shown below.

Using the above table, find events happening on Tuesday and the exact time for them. Use the query below.

SELECT * FROM Events
WHERE DATENAME(weekday, EventDate) = 'Tuesday';

After executing the above query, it returns the result set containing all the event names with the times they will happen on a Tuesday. For example, the event Music Festival will happen on 2023-11-21 20:00:00.45000000.

So when you provide the date part as weekday to the DATENAME(weekday, EventDate) function, it returns the weekday’s name.

This is how you can use the DATENAME function in SQL Server with a table to extract the name of the day of the given datetime value.

Now look at the below query with output,

-- Current Weekday
SELECT DATENAME(weekday, current_timestamp) AS CurrentWeekday; -- Output: Wednesday

-- Year
SELECT DATENAME(year, current_timestamp) AS CurrentYear;       -- Output: 2023

-- Quarter
SELECT DATENAME(quarter, current_timestamp) AS CurrentQuarter;  -- Output: 4

-- Month
SELECT DATENAME(month, current_timestamp) AS CurrentMonth;  -- Output: November

-- Day of Year
SELECT DATENAME(dayofyear, current_timestamp) AS DayOfYear; -- Output: 333

-- Day
SELECT DATENAME(day, current_timestamp) AS CurrentDay;     -- Output: 29

-- Week
SELECT DATENAME(week, current_timestamp) AS CurrentWeek;   -- Output: 48

-- Hour
SELECT DATENAME(hour, current_timestamp) AS CurrentHour;   -- Output: 14

-- Minute
SELECT DATENAME(minute, current_timestamp) AS CurrentMinute; -- Output: 35

-- Second
SELECT DATENAME(second, current_timestamp) AS CurrentSecond; -- Output: 51

-- Millisecond
SELECT DATENAME(millisecond, current_timestamp) AS CurrentMillisecond; -- Output: 273

As you can see, the query returns each date as part of the current time and value.

Conclusion

In this SQL Server tutorial, you learned how to find the date part of the date such as year, month, weekday, minute, and second using the DATENAME() function in SQL Server. Also, you have used the DATENAME() function on the table to find the weekday of the events.

You may like to read:

• SYSUTCDATETIME Function in SQL Server
• How to use SYSDATETIME Function in SQL Server
• SYSDATETIMEOFFSET Function in SQL Server

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.