SQL Server DAY() Function Overview
Structured Query Language (SQL) is the standard language used to manage and manipulate relational databases. SQL Server DAY() function is a built-in function used to extract the day component from a given date value.
The DAY() function is straightforward and easy to use, but it is essential to understand its syntax and examples to use it correctly. In this article, we will explore the DAY() function’s syntax, examples, and common methods to use it in a relational database management system.
The DAY() function in SQL Server returns an integer value representing the day part of a given date value. The returned value ranges from 1 to 31, based on the specified date. The DAY() function can be applied to any date, time, or datetime data type.
The function is part of the DATEPART() function family, which is used to extract specific components of a datetime value.
SQL Server DAY() Function Syntax
The syntax for the DAY() function is simple and consists of only one argument:
DAY(expression)
The expression is the argument that represents the date from which we want to extract the day. The expression can represent any of the following data types:
- TIME
- DATE
- SMALLDATETIME
- DATETIME
- DATETIME2
- DATETIMEOFFSET
It is essential to note that the expression argument must be a valid input for the data type specified.
If the expression is an invalid data type, it will return an error.
SQL Server DAY() Function Examples
The DAY() function has several examples based on the data type used. The following examples illustrate the usage of the DAY() function:
Using DAY() Function with Literal Date Value
We can use the DAY() function with a literal date value to extract the day part. For example, if we want to extract the day part from December 1, 2030, we can use the following command:
SELECT DAY('2030-12-01')
The output of this query would be:
1
This means that December 1 is the first day of the month.
Using DAY() Function with Time Part
We can also use the DAY() function to extract the day part from a datetime value that includes a time part. For example, consider the following datetime value:
DECLARE @date DATETIME = '2030-12-01 05:30:45.123'
SELECT DAY(@date)
The output of this query is:
1
This means that the day part of the given datetime value is 1.
Using DAY() Function with Table Columns
We can also use the DAY() function with table columns to extract the day part from a specific date column. For example, consider the following table created with sample data:
CREATE TABLE sales (
sale_date DATE,
sale_amount FLOAT
);
INSERT INTO sales(sale_date, sale_amount)
VALUES('2030-12-01', 100.50),
('2030-12-02', 200.50),
('2030-12-21', 150.75),
('2031-01-01', 300.00);
To extract the day component of the sale_date column, we can use the following SQL query:
SELECT DAY(sale_date) as day, SUM(sale_amount) as total_sales
FROM sales
GROUP BY DAY(sale_date)
The output of this query is:
day | total_sales
---- | -----------
1 | 100.50
2 | 200.50
21 | 150.75
1 | 300.00
This query groups the total sales by day, returning the sum of sale_amount for each day.
Using DAY() Function with Date Value that has Only Time Part
Although it might seem unusual, sometimes we may have to extract the day component from a date value that has only a time part and no actual date. In this scenario, the DAY() function can still be used, but we need to be careful about what we feed it.
For instance, consider a scenario where we want to extract the day of the month from a literal time value like ’10:20:30′. In this case, DAY() will extract the day of the month from the current system date rather than the literal value.
To avoid this issue, we can combine the DATEVALUE() function with the TIME() function to convert the time value to a proper datetime value first, as follows:
SELECT DAY(DATEVALUE('2000-01-01') + TIME('10:20:30'))
The output of this query would be:
1
This will extract the day component from the date value ‘2000-01-01 10:20:30’ – the date component does not matter since it’s just a placeholder in this example.
Using DAY() Function with Table Columns Example
In a production environment, you might need to extract the day component from one of your tables’ date columns.
This operation can be performed with the DAY() function, among other functions. To illustrate, we will consider an example with two tables – a sales.orders table and a sales.order_items table – which have the following columns:
sales.orders:
- order_id
- customer_id
- order_date
- shipped_date
- ship_via
- freight
sales.order_items:
- order_id
- product_id
- unit_price
- quantity
- discount
Suppose we want to calculate the total sales grouped by day for January 2021. In this case, we need to extract the day part from the shipped_date column and filter by year and month, as the following example shows:
SELECT DAY(shipped_date) as day,
SUM(unit_price * quantity - discount) as gross_sales
FROM sales.orders
JOIN sales.order_items ON sales.orders.order_id = sales.order_items.order_id
WHERE YEAR(shipped_date) = 2021
AND MONTH(shipped_date) = 01
GROUP BY DAY(shipped_date)
The output of this query would be a table with two columns – day and gross_sales – which contains the total gross sales for each day in January 2021:
day | gross_sales
--- | -----------
1 | 12500.5
2 | 16500.
12 | 18750.29
...
30 | 14300.45
31 | 13250.78
In this query, we join the orders and order_items tables to obtain a list of all items shipped during January 2021. We then filter those by year and month of the shipped_date column.
The DAY() function extracts the day component from the shipped_date to group the gross_sales by day. Finally, we use the SUM() and GROUP BY clauses to calculate the total gross sales for each day.
Note that other date functions such as YEAR() and MONTH() were used in combination with DAY() to achieve our goal. It is often the case that these functions will be used alongside DAY() to meet queries’ date requirements.
Conclusion
In this article, we have explored the SQL Server DAY() function, which is used to extract the day component from a specified date. The DAY() function is straightforward and easy to use, but it is essential to understand its syntax and examples to use it correctly.
We started by providing the basic overview of the DAY() function, its primary keywords, and the data types it operates on. We then delved into the syntax of the function and how it takes an expression as an argument representing the date from which we want to extract the day.
We also discussed the different data types that can be used as an expression and the limitations.
Next, we provided examples to illustrate how the DAY() function can be used in different scenarios.
We explained how to extract the day part from a literal date value, time part, and table columns.
Finally, we discussed in detail how the function can be used to group data by day when calculating gross sales in a specified date range.
We saw that it was necessary to use other date functions such as YEAR() and MONTH() alongside DAY() to meet the requirements of the query. Extracting the day component from a date is a common operation in a relational database management system.
The DAY() function, among other date functions, comes in very handy in such a scenario. To use it appropriately, it is essential to understand its syntax and limitations in different contexts.
In conclusion, the SQL Server DAY() function is essential in manipulating and managing date-related data. Its simple syntax and intuitive utilization make it an accessible tool for anyone working with SQL Server.
By understanding the examples discussed in this article, you can confidently apply the DAY() function in your own queries to extract the day component from any date. The SQL Server DAY() function is a built-in function that can extract the day component from a given date value.
We have seen that the DAY() function can be applied to any date, time, or datetime data type. In this article, we have discussed the syntax and examples of the function and seen how it can be used to extract the day component from a literal date value, time part, and table columns.
We have also seen how the function can be used to group data by day when calculating gross sales in a specified date range. The DAY() function is a versatile tool for anyone working with SQL Server and a valuable resource for data manipulation and management.