How the DAY() function works in Mariadb?

The MariaDB DAY() function is used to extract the day of the month from a given date, returning an integer value between 1 and 31. This function is particularly useful for reporting, data analysis, and any scenario where the day component of a date is required.

Syntax

The syntax for the MariaDB DAY() function is as follows:

DAY(date)

date is the date from which you want to extract the day.

Examples

Example 1: Extracting the Day from a Date

To extract the day from a specific date:

SELECT DAY('2024-03-17') AS Day_Of_Month;

The output will be:

+--------------+
| Day_Of_Month |
+--------------+
|           17 |
+--------------+

Example 2: Finding the Current Day of the Month

To find the current day of the month:

SELECT DAY(CURDATE()) AS Today_Day;

The output will show today’s day of the month:

+-----------+
| Today_Day |
+-----------+
|        17 |
+-----------+

Example 3: Filtering Data by Day of the Month

To filter data based on the day of the month:

DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
    id INT,
    appointment_date DATE
);
INSERT INTO appointments VALUES (1, '2024-03-17'), (2, '2024-03-18');

SELECT * FROM appointments WHERE DAY(appointment_date) = 17;

The output will display appointments on the 17th day of the month:

+------+------------------+
| id   | appointment_date |
+------+------------------+
|    1 | 2024-03-17       |
+------+------------------+

Example 4: Grouping Data by Day of the Month

To group data by the day of the month:

SELECT DAY(appointment_date) AS Day, COUNT(*) AS Total_Appointments
FROM appointments
GROUP BY Day;

The output will show the count of appointments grouped by day:

+------+--------------------+
| Day  | Total_Appointments |
+------+--------------------+
|   17 |                  1 |
|   18 |                  1 |
+------+--------------------+

Example 5: Using `DAY()` with `ORDER BY`

To order data by the day of the month:

SELECT * FROM appointments ORDER BY DAY(appointment_date);

The output will order the appointments by the day of the month:

+------+------------------+
| id   | appointment_date |
+------+------------------+
|    1 | 2024-03-17       |
|    2 | 2024-03-18       |
+------+------------------+

Here are a few functions related to the MariaDB DAY() function:

MariaDB MONTH() function is used to extract the month from a date.
MariaDB YEAR() function is used to extract the year from a date.
MariaDB DAYOFWEEK() function returns the weekday index for a date (1 = Sunday, 2 = Monday, …, 7 = Saturday).

Conclusion

The DAY() function in MariaDB is a simple yet powerful tool for extracting the day part of a date. It enables users to perform day-specific queries and calculations, which can be essential for a wide range of applications, from scheduling systems to financial reports. Understanding how to use the DAY() function, along with related date and time functions, allows for more precise and effective data manipulation and analysis.