SQL - DAY() Function

---

---

The SQL DAY() function provides an integer value that indicates the day of the month (from 1 to 31) of the given date. The argument must be an expression that returns a value of one of the built-in data types of date, timestamp, character string, or graphic string.

The expression must be a valid string representation of a date or timestamp with an actual length of not more than 255 bytes if it is a character or graphic string, and it must not be a CLOB or DBCLOB.

Syntax

Following is the syntax for the SQL DAY() function −

DAY(date)

Parameters

This function accepts one parameter as discussed below −

• date − used to return the da of month from.

Example

In the following example where we are going to mention the invalid string and retriveing the day −

SELECT DAY(2022-11-11);

Output

When we execute the above query, the output is obtained as follows −

+-----------------+
| DAY(2022-11-11) |
+-----------------+
| 24              |
+-----------------+

Example

Let's look into the following example where we are going to retrieve the day using the following query −

SELECT DAY('');

Output

When we execute the above query, the output is obtained as follows −

+---------+
| DAY('') |
+---------+
| NULL    |
+---------+

Example

Here, we are trying to retrieve the day of month for a date using the following query −

SELECT DAY('2023/02/16') AS DayOfMonth;

Output

When we execute the above query, the output is obtained as follows −

+------------+
| DayOfMonth |
+------------+
| 16         |
+------------+

Example

Here, we are trying to find the day where only the time part was mentioned using the following query −

SELECT DAY('12:28:06') AS DAY;

Output

On executing the above query, the output is displayed as follows −

+-----+
| DAY |
+-----+
| 1   |
+-----+

Example

In the following example we are going to retrieve the day using CURRENT_TIMESTAMP execute the below query to do so.

SELECT DAY(CURRENT_TIMESTAMP);

Output

The output for the above query is produced as given below −

+------------------------+
| DAY(CURRENT_TIMESTAMP) |
+------------------------+
| 16                     |
+------------------------+

Example

Look at the following example, where we are using the alternative function FORMAT() to retrieve the date by using the following query −

SELECT FORMAT(CAST('2023-02-16' AS DATE), 'dddd');

Output

If we execute the above query, the result is produced as follows −

+----------+
| DAY      |
+----------+
| Thursday |
+----------+

Example

Considering the following example, where we are using the day() function with a variable and retrieving the day of the month from the specified date by using the following query −

DECLARE @DATE VARCHAR(25);
SET @DATE = '2023/02/16';
SELECT DAY(@DATE) AS DAY ;

Output

On executing the above query, it will generate the following output as shown below −

+------+
| DAY  |
+------+
| 16   |
+------+

Example

We can also use column name as an argument to the DAY() function. Let us create a table with the name IPLPlayers in the SQL database using the CREATE statement as shown in the query below −

CREATE TABLE IPLPlayers(
   ID INT,
   Name VARCHAR(255),
   Date_Of_Birth date,
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, let us insert some records in the IPLPlayers table using INSERT statements as shown in the query below −

INSERT INTO IPLPlayers VALUES (1, 'Shikhar','1999-11-10','India');
INSERT INTO IPLPlayers VALUES (2, 'Dhoni','1992-12-11','Jharkhand');
INSERT INTO IPLPlayers VALUES (3, 'Gayle','1993-10-12','Westindies');
INSERT INTO IPLPlayers VALUES (4, 'Williamson','1992-09-23','NewZealand'); 

We can verify whether the table IPLPlayers is created or not using the following query −

SELECT * FROM IPLPlayers;

The table IPLPlayers is successfully created in the SQL database −

+----+------------+---------------+------------+
| ID | Name       | Date_Of_Birth | Country    |
+----+------------+---------------+------------+
|  1 | Shikhar    | 1999-11-10    | India      |
|  2 | Dhoni      | 1992-12-11    | Jharkhand  |
|  3 | Gayle      | 1993-10-12    | Westindies |
|  4 | Williamson | 1992-09-23    | NewZealand |
+----+------------+---------------+------------+

Now we are going to retrieve the day from the Date_Of_Birth column of the table by running the following query −

SELECT Name,Date_Of_Birth, Country,
DAY(Date_Of_Birth) FROM IPLPlayers;

Output

When we execute the above query, the output is obtained as follows −

+------------+---------------+------------+--------------------+
| Name       | Date_Of_Birth | Country    | DAY(Date_Of_Birth) |
+------------+---------------+------------+--------------------+
| Shikhar    | 1999-11-10    | India      |                 10 |
| Dhoni      | 1992-12-11    | Jharkhand  |                 11 |
| Gayle      | 1993-10-12    | Westindies |                 12 |
| Williamson | 1992-09-23    | NewZealand |                 23 |
+------------+---------------+------------+--------------------+