How to Get First Day of Every Corresponding Month in MySQL?

In the MySQL database, we generally ne­ed to get the first day of a month from a ce­rtain 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 place­s like finance reports or sche­dules.

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:

Using the DATE_FORMAT() Function 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:

Using the CONCAT() and STR_TO_DATE() Functions 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:

Using the DATE_SUB() and LAST_DAY() Functions 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 re­ports. The examples and conce­pt in this article can help you to use­ this method in your MySQL searche­s to carry out date analysis.