SQL Server DATENAME() Function

The SQL Server DATENAME() function is used to return the name of a specified part of a date value. It can return the names of parts such as year, month, day, hour, minute, second, and so on.

Syntax

DATENAME(datepart, date)

• datepart - the part of the date to return. Optional values include: year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond.
• date - the date value from which to extract the specified part.

Usage

The DATENAME() function is useful in scenarios where you need to extract a specific part of a date value. For example, you may need to get the month or day of the week of a certain date, or summarize data by year, month, or quarter.

Examples

Here are two examples of using the DATENAME() function:

Example 1

Suppose you have a table called Sales that contains fields such as date, sales amount, and region. You want to calculate the total sales for each quarter. You can use the DATENAME() function to extract the quarter from the date and then use the SUM() function to calculate the total sales.

SELECT DATENAME(quarter, SaleDate) AS Quarter, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY DATENAME(quarter, SaleDate)
ORDER BY DATENAME(quarter, SaleDate)

Output:

Example 2

Suppose you have a table called Orders that contains fields such as order ID, order date, and delivery date. You want to calculate the delivery time of each order in days. You can use the DATEDIFF() function to calculate the delivery time in days, and then use the DATENAME() function to extract the number of days.

SELECT OrderID, DATENAME(day, DATEDIFF(day, OrderDate, DeliveryDate)) AS DeliveryTime
FROM Orders

Output:

Conclusion

The DATENAME() function is a very useful date function that can be used to extract a specific part of a date value. It is particularly useful when grouping, filtering, or calculating by date. By using the DATENAME() function in a SELECT statement, you can easily extract the specified part of a date value.