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 : 2018-12-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 : 2018-12-31;
Visitors Rating
Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer