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
The DAY() function is commonly used to query and summarize data for a specific month. For example, in a sales analysis query, the DAY() function can be used to obtain the sales for each day of the month.
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:
| OrderDate | Amount |
|---|---|
| 2022-01-01 | 1000 |
| 2022-01-02 | 2000 |
| 2022-01-05 | 1500 |
| 2022-02-01 | 3000 |
| 2022-02-02 | 2500 |
| 2022-02-05 | 1800 |
To query the sales for each day in January 2022, we can use the following SQL statement:
SELECT DAY(OrderDate) AS Day, SUM(Amount) AS Sales
FROM SalesTable
WHERE YEAR(OrderDate) = 2022 AND MONTH(OrderDate) = 1
GROUP BY DAY(OrderDate)
The result is:
| Day | Sales |
|---|---|
| 1 | 1000 |
| 2 | 2000 |
| 5 | 1500 |
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:
| EmployeeName | HireDate |
|---|---|
| John Smith | 2022-01-01 |
| Jane Doe | 2022-01-05 |
| Bob Johnson | 2022-02-01 |
| Tom Williams | 2022-02-05 |
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:
| EmployeeName | HireDay |
|---|---|
| John Smith | 1 |
| Jane Doe | 5 |
| Bob Johnson | 1 |
| Tom Williams | 5 |
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.