Articles Cookbook
Back to list
MySQL

How to Find the Last Day of the Month for a Given Date in MySQL

Database:

Operators:

LAST_DAY()

Problem:

You’d like to get the date of the last day of the month for a date in a MySQL database.

Example:

Our database has a table named car_sales with data in the columns id, make, model, and sale_date.

idmakemodelsale_date
1FordKa2019-02-01
2ToyotaYaris2019-08-15
3OpelCorsa2019-06-22

For each car, let’s get the make and model and the last day of the month in which the car was sold. (The last day of the month is one of the terms of the transaction settlement.)

Solution:

We’ll use the LAST_DAY() function. Here’s the query you’d write:

SELECT make,
       model,
       LAST_DAY(sale_date) 
         AS  sale_last_day
FROM car_sales;

Here’s the result of the query:

makemodelsale_last_day
FordKa2019-02-28
ToyotaYaris2019-08-31
OpelCorsa2019-06-30

Discussion:

Use the LAST_DAY() function if you want to retrieve the last day of the month for a date in MySQL. This function takes one argument – the date, either as an expression returning a date/datetime/timestamp value or the name of a date/datetime/timestamp column. In our example, we use the sale_date column. It’s of the date data type.

LAST_DATE() returns the last day of the month of a given date. In our example, the last day of the month when the Ford Ka was sold is ‘2019-02-28’; the car sold on ‘2019-02-01’, and the last day of February 2019 was ‘2019-02-28’.

Recommended courses:

Recommended articles:

See also:

go to top