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:
- Using the YEAR, MONTH and LAST_DAY function
- Using intervals
- 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:
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:
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:
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:
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:
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.
Contact Us