How to use the DATE_SUB() and LAST_DAY() Functions In MySQL
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.
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.
Contact Us