How to use the MySQL DAY() function

The DAY() function in MySQL is used to get the day of the month from a date value. It returns the day number (1-31) for the given date.

Syntax

The syntax for DAY() is:

DAY(date)

Where date is the date value to extract the day from.

Examples

1. Get the day of the month from a date:

   SELECT DAY('2023-02-15');
   

   Returns 15, the day portion of the date.

2. Use DAY() to filter dates:

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

   Returns orders placed on 1st day of the month.

3. Get the day from a date column:

   SELECT DAY(birthday) FROM users;
   

   Returns just the day part of each user’s birthday.

4. Use DAY() in a WHERE clause:

   SELECT * FROM billing
   WHERE DAY(billing_date) BETWEEN 1 AND 15;
   

   Returns rows where billing day is between 1-15.

5. Get day name from date using WEEKDAY():

   SELECT WEEKDAY('2023-02-15');
   

   Returns 3, corresponding to Wednesday.

Other Similar Functions

• WEEKDAY() - Day name from date
• DAYOFWEEK() - Index of day in week
• DAYOFMONTH() - Day of month (same as DAY())
• DAYOFYEAR() - Day number in year

So DAY() provides an easy way to get the day from a date in MySQL queries.