How to Get First Day of Every Corresponding Month in MySQL?
In the MySQL database, we generally need to get the first day of a month from a certain date. While there are many tasks related to dates, one key job is figuring out the first day of each month is an important need. This is important in many places like finance reports or schedules.
This is usual for data analysis and reports. Getting the first day of every month in MySQL is very important for date-oriented tasks. In this article, we will learn how to get the first day in every corresponding month in MySQL using date functions.
How to Retrieve the First Day of the Month in MySQL
Extracting the first day of the month in MySQL is a common requirement for date-oriented tasks. Here, we’ll explore three approaches to achieve this efficiently.
- Using the DATE_FORMAT() Function
- Using the CONCAT() and STR_TO_DATE() Functions
- Using the DATE_SUB() and LAST_DAY() Functions
Let’s Setup an Environment
Let’s create a table named students to perform the examples for all the three approaches:
CREATE TABLE students (
id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
age int DEFAULT NULL,
grade varchar(2) DEFAULT NULL,
joining date DEFAULT NULL
)
Using the DATE_FORMAT() Function
The DATE_FORMAT() function in MySQL allows formatting dates according to specific patterns. It’s commonly used to manipulate date representations, enabling tasks like extracting the first day of the month efficiently.
Syntax:
DATE_FORMAT(DATE_ADD(date_column, INTERVAL 1 - DAYOFMONTH(date_column) DAY), '%Y-%m-01') AS first_day
Example: Using the DATE_FORMAT() Function
In this approach, we will manipulate the date using DATE_FORMAT() and DATE_ADD() functions to format it as the first day of a month.
SELECT
name,
joining,
DATE_FORMAT(DATE_ADD(joining, INTERVAL 1 - DAYOFMONTH(joining) DAY), '%Y-%m-01') AS first_day
FROM
students limit 4;
Output:
Explanation:
- The DATE_ADD() is used to add or subtract days from the joining date to get the first day of that month.
- This function returns an integer representing the day of the month for the given date.
- The DATE_FORMAT() formats generates a “YYYY-MM-01” string which represents the starting day of a month.
Using the CONCAT() and STR_TO_DATE() Functions
Syntax:
CONCAT(YEAR(date_column), '-', MONTH(date_column), '-01') AS first_day
Example: Using the CONCAT() and STR_TO_DATE() Functions
In this particular method, to create a string that represents the first day of the month, we shall use CONCAT() function along with STR_TO_DATE() function.
SELECT
name,
joining,
STR_TO_DATE(CONCAT(YEAR(joining), '-', MONTH(joining), '-01'), '%Y-%m-%d') AS first_day
FROM
students limit 4;
Output:
Explanation:
- This string, ‘concat(year(joining)), “-“, month(Joining), “-01”)’ will creating a string in the format of ‘YYYY-MM-01’ that represents the first day of the month.
- STR_TO_DATE() changes this string into a date format.
Using the DATE_SUB() and LAST_DAY() Functions
Syntax:
DATE_SUB(LAST_DAY(date_column), INTERVAL DAY(LAST_DAY(date_column)) - 1 DAY) AS first_day
Example: Using the DATE_SUB() and LAST_DAY() Functions
We will use the DATE_SUB() to extract month and year from the joining date, and LAST_DAY() for getting last day of the month. Then we can take away subtract 1 from day of the month to get first day.
SELECT
name,
joining,
DATE_SUB(LAST_DAY(joining), INTERVAL DAY(LAST_DAY(joining)) - 1 DAY) AS first_day
FROM
students LIMIT 4;
Output:
Explanation:
- LAST_DAY(joining): this function returns the last day of the month for joining date.
- DAY(LAST_DAY(joining)): this extracts the day from the date that is found at step before
- INTERVAL DAY(LAST_DAY(joining)) – 1 DAY: here we calculate the number of days that have to be subtracted from the last day to reach 1st day
- DATE_SUB(): this subtracts calculated duration from last day of the month and gives us first day.
Conclusion
To get the first day of each month from a date in MySQL, by using techniques like DATE_FORMAT(), manipulating strings with CONCAT() and STR_TO_DATE() functions, or applying DATE_SUB() and LAST_DAY() functions, you can have flexibility and accuracy in manipulating dates. These functions help in getting out this information for data analysis and reports. The examples and concept in this article can help you to use this method in your MySQL searches to carry out date analysis.
FAQs on How to Get the First Day of Every Corresponding Month in MySQL
Can these methods handle leap years and different month lengths?
Yes, these methods account for different month lengths and leap years, ensuring accurate results regardless of the specific month or year.
Which method is the most efficient?
All methods are efficient, but using
DATE_FORMAT()
andDATE_ADD()
might be slightly more readable and easier to maintain compared to others.
Why is it important to get the first day of the month in MySQL?
It is crucial for tasks such as financial reporting, scheduling, and various date-oriented analyses where the start of the month serves as a key reference point.
Contact Us