How to Get the First and Last Day of the Month in MariaDB?

In MariaDB, working with dates is a common task, and knowing how to manipulate them can be incredibly useful. One common requirement is to find the first and last day of the month for a given date. In this article, we’ll learn various methods like YEAR, MONTH, LAST_DAY and so on which are used to get the first and last day of the month.

How to Get the First and Last Day of the Month?

To get the first and last day of the month in MariaDB, we can use several approaches. We’ll discuss three methods. We will fetch the first and last day of the month in Mariadb using the below methods are as follows:

1. Using the YEAR, MONTH and LAST_DAY function
2. Using intervals
3. Using DATE_FORMAT and LAST_DAY Function

Before understanding these methods. Let’s take an overview of these functions.

YEAR Function

The YEAR function returns the year from the date object. It returns an integer in the range 1000 to 9999.

Syntax:

YEAR(date)

Example:

The following query retrieves the year value from the date object:

SELECT YEAR('2024-01-01');

Output:

year output

MONTH Function

The MONTH function returns the month from the date object. It returns an integer in the range 1 to 12.

Syntax:

MONTH(date)

Explanation: Here date is the date object.

Example:

The following query retrieves the month value from the date object:

SELECT MONTH('2024-01-01');

Output:

month output

LAST_DAY Function

The LAST_DAY function takes in a date object and returns the last day of the month corresponding to the date object.

Syntax:

LAST_DAY(date)

Explanation: Here date is the date object.

Example:

The following query retrieves the last day from the date object:

SELECT LAST_DAY('2024-01-01');

Output:

last day output

DATE_FORMAT Function

The DATE_FORMAT function is used to format the date in whatever form you like. The specify the format you need to pass the format string.

Syntax:

DATE_FORMAT(date, fmt)

Explanation: Here date is the date object and fmt is the format string.

Example:

The following query formats the current date to get the week, month and year value of the date object.

SELECT DATE_FORMAT(CURRENT_DATE(), '%W %M %Y');

Output:

date format output

Let’s setup an environment to get the first and last day of the month

To understand How to get the first and last day of the month in MariaDB we need a table on which we will perform various operations and queries. Here we will consider a table called test which contains id and val as Columns. Also, let’s insert some records into it.

CREATE TABLE test (
  id INTEGER,
  val DATE
);

INSERT INTO test VALUES (1, '2023-01-25');
INSERT INTO test VALUES (2, '2021-02-12');
INSERT INTO test VALUES (3, '2026-08-01');
INSERT INTO test VALUES (4, '1998-05-07');
INSERT INTO test VALUES (5, '2000-12-17');
INSERT INTO test VALUES (6, '2012-09-17');
INSERT INTO test VALUES (7, '2022-06-23');

Output:

Output

1. Using YEAR, MONTH and LAST_DAY function

As we have understood earlier in this article, YEAR, MONTH and LAST_DAY functions allows us to manipulate the dates in different fashions. We can make use of the functions in conjunction with CONCAT_WS function to find the relevant values. The following query uses the above functions to find the values:

SELECT id, DATE(CONCAT_WS('-', YEAR(val), MONTH(val), '01')) AS first_day, LAST_DAY(val) AS last_day
FROM test;

Output:

first and last day of month

Explanation: In the above query, we have retrieved the respective values using the functions and then concatenated them with the ‘-‘ separator.

2. Using Intervals

We can use the interval keyword to subtract and add specified amount of days to a date object. The following query uses the same to get the required values.

SELECT id, LAST_DAY(val) - interval 1 month + interval 1 day as first_day, LAST_DAY(val) AS last_day 
FROM test;

Output:

first and last day of month

Explanation: In the above query, to find the first_day we first subtracted 1 month using interval 1 month. This provides us with the last day of the previous month. We then add 1 day to the date using interval 1 day.

3. Using DATE_FORMAT and LAST_DAY Function

As we have already seen earlier, the DATE_FORMAT function can be used to format the date in whatever fashion we want it to be. We can use the same function to get relevant values. The following query uses the same to get the required values.

SELECT id, DATE_FORMAT(val ,'%Y-%m-01') as first_day, LAST_DAY(val) AS last_day
FROM test;

Output:

first and last day of month

Explanation: In the above query, we used the DATE_FORMAT function to retrieve the first day of the month using the format string ‘%Y-%m-01’.

Conclusion

In this article, we saw how we can find the first and last day of a month in MariaDB. We started by looking at several functions like YEAR, MONTH, LAST_DAY and DATE_FORMAT. We understood what they do and saw examples of them. We later saw several methods to find the required values. First we saw how to use the several functions, then saw intervals and finally we saw how we can use the DATE_FORMAT function.