SQL Server DAY Function

Summary: in this tutorial, you will learn how to use the SQL Server DAY() function to extract the day of the month from a specified date.

SQL Server DAY() function overview

The DAY() function returns an integer value which represents the day of a month (1-31) of a specified date.

The following illustrates the syntax of the DAY() function:

DAY(input_date)Code language: SQL (Structured Query Language) (sql)

The DAY() function takes one argument which can be a literal date value or an expression that can resolve to a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2, or DATETIMEOFFSET value.

The DAY() function returns the same value as the following DATEPART() function:

DATEPART(day,input_date)
Code language: SQL (Structured Query Language) (sql)

SQL Server DAY() function examples

Let’s take some examples of using the DAY() function.

A) Using DAY() function with a literal date value

This example uses the DAY() function to extract the day from the date '2030-12-01':

SELECT 
	DAY('2030-12-01') [DAY];
Code language: SQL (Structured Query Language) (sql)

Here is the output:

DAY
-----------
1

(1 row affected)

B) Using DAY() function with a date value that has only time part

The DAY() function will return 1 if the input date contains only time part:

SELECT 
	DAY('10:20:30') [DAY];
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

DAY
-----------
1

(1 row affected)

C) Using DAY() function with table columns example

We will use the sales.orders and sales.order_items from the sample database for demonstration.

This example uses the DAY() function to extract the day data from the values in the shipped_date column. It returns the gross sales by day in February 2017 using the SUM() function and GROUP BY clause:

SELECT 
    DAY(shipped_date) [day], 
    SUM(list_price * quantity) gross_sales
FROM 
    sales.orders o
    INNER JOIN sales.order_items i ON i.order_id = o.order_id
WHERE 
    shipped_date IS NOT NULL
    AND YEAR(shipped_date) = 2017
    AND MONTH(shipped_date) = 2
GROUP BY 
    DAY(shipped_date)
ORDER BY [day];    Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

In this tutorial, you have learned how to extract the day from a specified date by using the SQL Server DAY() function.