In SQL Server,  you can use the DAY() function to return the “day” part of a date. This function returns an integer that represents the day of the month (not the day of the week).

Below are examples of how to use this function.

Syntax

The syntax goes like this:

DAY ( date )

Where date is an expression that resolves to one of the following data types:

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

This can be a column expression, expression, string literal, or user-defined variable.

Example

Here’s a basic example of how it works:

SELECT 
   SYSDATETIME() AS 'Date',
   DAY(SYSDATETIME()) AS 'Day';

Result:

+-----------------------------+-------+
| Date                        | Day   |
|-----------------------------+-------|
| 2018-06-18 00:20:22.1284540 | 18    |
+-----------------------------+-------+

So the DAY() function was able to extract the day from the datetime2 value (which was returned by the SYSDATETIME() function).

Date Provided as a String Literal

Here’s an example where the date is provided as a string literal.

SELECT DAY('2019-01-07') AS Result;

Result:

+----------+
| Result   |
|----------|
| 7        |
+----------+

And here’s an example where the date is provided in a different format:

SELECT DAY('07/01/2017') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

However, it’s usually best to avoid using dates in such formats. If you must do so, you’ll need to be mindful of the language settings and/or the date format settings of the current session.

Language Settings

The output of the previous example will depend on the language settings and/or date format settings of the current session.

When we set the language, the date format is implicitly set at the same time.

Here’s what happens when we provide the same date argument in two different language environments.

British

SET LANGUAGE British;
SELECT DAY('07/01/2017') AS Result;

Result:

+----------+
| Result   |
|----------|
| 7        |
+----------+

us_English

SET LANGUAGE us_English;
SELECT DAY('07/01/2017') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

Date Format Settings

The date format settings can override the language settings, so you also need to be aware of this setting. For example, we could be using us_English for our language (which has a default date format of mdy), but we could override the date format to be dmy.

Here’s an example:

us_English – Default Date Format

Here, we set the language to us_English, which implicitly sets the date format to myy.

SET LANGUAGE us_English;
SELECT DAY('07/01/2017') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

us_English – Override Date Format

Here, we set the language to us_English (which implicitly sets the date format), but then we explicitly set the date format to dmy. This overrides the date format that was implicitly set when we set the language.

SET LANGUAGE us_English;
SET DATEFORMAT dmy;
SELECT DAY('07/01/2017') AS Result;

Result:

+----------+
| Result   |
|----------|
| 7        |
+----------+

Return the Day Name

If you need to return the day name (as opposed to the day number), see 3 Ways to Get the Day Name from a Date in SQL Server.