How to Use the DAY() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the DAY() function in MySQL.

Using the DAY() function in MySQL

In MySQL, the DAY() function is used to extract the day of the month from a date. The function returns the day of the month as an integer value, ranging from 1 to 31.

Syntax

DAY(date);

where date is the input date in any valid date format.

Examples

Here are some examples of using the DAY() function in MySQL:

Extracting the day from a date string

To extract the day from a date string, you can pass the string as an argument to the DAY() function.

Syntax
SELECT DAY('2022-05-15');
Example
SELECT DAY('2022-05-15');

Output:

+-----------------+
| DAY('2022-05-15') |
+-----------------+
|               15 |
+-----------------+

Extracting the day from a date column in a table

To extract the day from a date column in a table, you can use the DAY() function in the SELECT statement.

Syntax
SELECT DAY(date_column) FROM table_name;
Example

Consider a table orders with columns order_id, order_date, and order_total. To extract the day from the order_date column, you can use the following query:

SELECT DAY(order_date) FROM orders;

Output:

+---------------+
| DAY(order_date) |
+---------------+
|             10 |
|             25 |
|             15 |
|             23 |
+---------------+

Using DAY() function with WHERE clause

You can also use the DAY() function in a WHERE clause to filter records based on the day of the month.

Syntax
SELECT column1, column2, ...
FROM table_name
WHERE DAY(date_column) = day_value;
Example

To select orders placed on the 15th day of the month, you can use the following query:

SELECT order_id, order_total
FROM orders
WHERE DAY(order_date) = 15;

Output:

+----------+-------------+
| order_id | order_total |
+----------+-------------+
|     1001 |       250.00 |
|     1003 |      1345.00 |
+----------+-------------+
#MySQL