LAST_DAY() Last Day of the month in MySQL Query

SELECT LAST_DAY(  '2016-12-25' )
Output is 2016-12-31

Syntax of LAST_DAY()
LAST_DAY(date)
DEMO: Select date from Calendar to execute LAST_DAY()

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 : 2024-03-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 : 2024-03-31;
SQL Date References MONTH() DAY() NOW() : Current Date with Time DAYOFWEEK() : Get weekdays like Sunday , Monday .. etc.
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer