How the DAYOFMONTH() function works in Mariadb?

The MariaDB DAYOFMONTH() function is used to extract the day of the month from a given date, returning an integer between 1 and 31. This function is essential for tasks that require the analysis or display of day-specific data within a month, such as generating monthly reports, scheduling monthly payments, or organizing calendar events.

Syntax

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

DAYOFMONTH(date)

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

Examples

Example 1: Extracting Day of Month from a Date

To extract the day of the month from a specific date:

SELECT DAYOFMONTH('2024-03-17') AS DayOfMonth;

The output will be:

+------------+
| DayOfMonth |
+------------+
|         17 |
+------------+

Example 2: Current Day of the Month

To find out the current day of the month:

SELECT DAYOFMONTH(CURDATE()) AS CurrentDayOfMonth;

The output will show the current day of the month:

+-------------------+
| CurrentDayOfMonth |
+-------------------+
|                17 |
+-------------------+

Example 3: Filtering Records by Day of the Month

To filter records based on the day of the month:

DROP TABLE IF EXISTS events;
CREATE TABLE events (
    id INT,
    event_date DATE,
    event_name VARCHAR(255)
);
INSERT INTO events VALUES (1, '2024-03-17', 'St. Patrick''s Day'), (2, '2024-03-18', 'Meeting');

SELECT * FROM events WHERE DAYOFMONTH(event_date) = 17;

The output will display events that occur on the 17th day of the month:

+------+------------+-------------------+
| id   | event_date | event_name        |
+------+------------+-------------------+
|    1 | 2024-03-17 | St. Patrick's Day |
+------+------------+-------------------+

Example 4: Grouping Records by Day of the Month

To group records by the day of the month:

SELECT DAYOFMONTH(event_date) AS Day, COUNT(*) AS TotalEvents
FROM events
GROUP BY Day;

The output will show the count of events grouped by day of the month:

+------+-------------+
| Day  | TotalEvents |
+------+-------------+
|   17 |           1 |
|   18 |           1 |
+------+-------------+

Example 5: Ordering Records by Day of the Month

To order records by the day of the month:

SELECT * FROM events ORDER BY DAYOFMONTH(event_date);

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

+------+------------+-------------------+
| id   | event_date | event_name        |
+------+------------+-------------------+
|    1 | 2024-03-17 | St. Patrick's Day |
|    2 | 2024-03-18 | Meeting           |
+------+------------+-------------------+

Here are a few functions related to the MariaDB DAYOFMONTH() 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 DAYOFMONTH() function in MariaDB is a straightforward and effective way to work with the day component of dates. It allows for precise querying and manipulation of date-related data, which is invaluable for a wide range of applications that depend on monthly cycles and schedules. By understanding how to use DAYOFMONTH() and related functions, developers can enhance the functionality and user experience of their database-driven applications.