LAST_DAY() to get last day of the month in MySQL

LAST_DAY() Last Day of the month in MySQL Query

Syntax of LAST_DAY()

LAST_DAY(date)

Example

SELECT LAST_DAY(  '2016-12-25' )

Output is 2016-12-31 With datetime

SELECT LAST_DAY('2016-09-13 12:59:45')

Output is 2016-09-30 We can use DATE_FORMAT() to change the output

select DATE_FORMAT(LAST_DAY('2016-09-15'),'%W %D %M %Y')

Output is Friday 30th September 2016

What is the last date of current month? Now() returns the present date and time

SELECT LAST_DAY( NOW( ) )

LAST DAY OF Previous Month

SELECT LAST_DAY(CURDATE() - INTERVAL 1 MONTH)

CURDATE() to get First day and last day of previous Month

Present Month Records

Starting from 1st day of the current month till now. ( there is no FAST_DAY() function to get the 1st day of the month).

SELECT * FROM `dt_table` WHERE  date between  DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND CURDATE()

Get the last working day of the month

Sunday is not a working day of the month, so to get the last working day of the month we have to use weekday() function and then use IF function to check condition to change the date by 1 if last_day() returns SUNDAY , to get the last working day of the month.

SELECT IF( DAYOFWEEK( LAST_DAY( NOW( ) ) ) =1, DATE_ADD( LAST_DAY( NOW( ) ) , INTERVAL -1 DAY ) , LAST_DAY( NOW( ) ) )

Try this

SELECT IF( DAYOFWEEK( LAST_DAY( '2016-07-20' ) ) =1, DATE_ADD( LAST_DAY( '2016-07-20' ) , INTERVAL -1 DAY ) , LAST_DAY( '2016-07-20' ) )

Output is 2016-07-30

Now let us try again the last date of current month. Output is : 2022-05-31

PHP Script

To display the above details we used PHP Script, you can use the same script for using all above queries.

To run this PHP Script have to first connect to MySQL database. All details of connection string is available here.
We have used PDO for executing Queries in PHP, details on how to get records using FETCH() are here.

<?Php
require 'config.php';  // Database connection string
$query='SELECT LAST_DAY( NOW( )) as dt';
// Change the above Query part to get different results // 

$count=$dbo->prepare($query);
$count->execute();
$row = $count->fetch(PDO::FETCH_OBJ);
echo 'Output is : '.$row->dt; 
?>

Output is : 2022-05-31;

This article is written by plus2net.com team. https://www.plus2net.com

plus2net.com

▼ More on Date & Time functions in SQL

LAST_DAY() Last Day of the month in MySQL Query

LAST DAY OF Previous Month

Present Month Records

Get the last working day of the month

PHP Script

Subscribe