SQL Server DAY() Function

In SQL Server, the DAY() function is used to extract the day of the month from a date expression. This function returns an integer value representing the day of the month in the date expression.

Syntax

DAY(date)

Parameter:

• date: Required. The date expression from which to extract the day.

Usage

After filtering rows to a particular month, DAY() can group them by day of the month. Filter a date/time column with a half-open range (>= the first day and < the first day of the next month) instead of applying YEAR() and MONTH() to the column in the WHERE clause. Applying functions to search-predicate columns generally makes a predicate non-SARGable and can prevent an index seek; see Microsoft’s SQL Server high-CPU troubleshooting guidance.

Examples

The following are two examples of using the DAY() function:

Example 1

Suppose we have a sales table that contains two fields, OrderDate and Amount, with the following data:

To query the sales for each day in January 2022, filter OrderDate to the month and group the matching rows by day:

DECLARE @start_date date = '20220101';
DECLARE @end_date date = '20220201';

SELECT DAY(OrderDate) AS Day, SUM(Amount) AS Sales
FROM SalesTable
WHERE OrderDate >= @start_date
  AND OrderDate < @end_date
GROUP BY DAY(OrderDate)
ORDER BY Day;

The result is:

This example uses the DAY() function and the SUM() function to obtain the sales for each day in January 2022.

Example 2

Suppose we have an employee table that contains two fields, EmployeeName and HireDate, with the following data:

To query the hire day for each employee, we can use the following SQL statement:

SELECT EmployeeName, DAY(HireDate) AS HireDay
FROM EmployeeTable

The result is:

This example uses the DAY() function to obtain the hire day for each employee.

Conclusion

The DAY() function is a commonly used date function in SQL Server that can be used to extract the day of the month from a date expression. It can be useful in querying and summarizing data, especially when dealing with time-series data.