MySQL DAY Function

MySQL DAY() Function

The DAY() function in MySQL extracts the day (as a number) from a given date or datetime value. It returns an integer between 1 and 31, representing the day of the month.

Syntax

DAY(date)

Parameter:

• date: A valid DATE, DATETIME, or TIMESTAMP value from which the day will be extracted.

Return Value:

• Returns an integer (1-31) representing the day of the month.
• Returns NULL if the input is NULL or an invalid date.

Examples

1. Extracting the Day from a Date

SELECT DAY('2025-07-15') AS day_number;

Output:

+------------+
| day_number |
+------------+
| 15         |
+------------+

2. Extracting the Day from a Datetime

SELECT DAY('2025-12-25 14:30:00') AS day_number;

Output:

+------------+
| day_number |
+------------+
| 25         |
+------------+

3. Using DAY() on a Table Column

Assume we have an orders table:

SELECT order_id, order_date, DAY(order_date) AS order_day
FROM orders;

This query will return the day of the month for each order.

4. Using DAY() in a WHERE Clause

Find all records from the 10th day of any month:

SELECT * FROM orders WHERE DAY(order_date) = 10;

Handling NULL and Invalid Dates

SELECT DAY(NULL) AS result; -- Output: NULL
SELECT DAY('2025-15-30') AS result; -- Output: NULL (Invalid Date)

Alternative: Extracting Full Date Components

If you need more than just the day, you can use:

• MONTH() – Extracts the month number.
• YEAR() – Extracts the year.
• DAYOFWEEK() – Returns the day of the week (1 = Sunday, 7 = Saturday).

Example:

SELECT YEAR('2025-07-15') AS year,
       MONTH('2025-07-15') AS month,
       DAY('2025-07-15') AS day;

Output:

+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2025 | 7     | 15  |
+------+-------+-----+

Conclusion

• DAY() extracts the day from a date (1-31).
• Useful for filtering and grouping data by day.
• Can be combined with MONTH() and YEAR() for advanced queries.

Great for date-based analysis in MySQL! 🚀