Menu
How can I get the first and last day of next month to be used in the where clause?
344
SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0) ): in this we have taken out the difference between the months from 0 to current date and then add 1 to the difference and 0 this will return the first day of next month.
Syntax : = EOMONTH(start_date, months) If we set parameter months as 0, then EOMONTH will output the last day of the month in which StartDate falls.
To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days. In this case, the enddate is arrival and the startdate is departure .
SET @firstday = '2013-04-01'; SELECT ADDDATE( @firstday , MOD((9-DAYOFWEEK(@firstday)),7)) as first_monday; The param @firstday is the first day of the month you want to search. Note that sunday is the first day of a week, monday is the second day. The answer was helpful.
Use:
SELECT DATE_SUB( LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) ), INTERVAL DAY( LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) ) )-1 DAY ) AS firstOfNextMonth, LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) )AS lastOfNextMonth For the last day of next month, you can use the LAST_DAY() function:
SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)); +-------------------------------------------------+ | LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) | +-------------------------------------------------+ | 2010-07-31 | +-------------------------------------------------+ 1 row in set (0.00 sec) Some tested edge cases:
SELECT LAST_DAY(DATE_ADD('2010-01-31', INTERVAL 1 MONTH)); +----------------------------------------------------+ | LAST_DAY(DATE_ADD('2010-01-31', INTERVAL 1 MONTH)) | +----------------------------------------------------+ | 2010-02-28 | +----------------------------------------------------+ 1 row in set (0.00 sec) SELECT LAST_DAY(DATE_ADD('2010-02-28', INTERVAL 1 MONTH)); +----------------------------------------------------+ | LAST_DAY(DATE_ADD('2010-02-28', INTERVAL 1 MONTH)) | +----------------------------------------------------+ | 2010-03-31 | +----------------------------------------------------+ 1 row in set (0.00 sec) SELECT LAST_DAY(DATE_ADD('2010-08-31', INTERVAL 1 MONTH)); +----------------------------------------------------+ | LAST_DAY(DATE_ADD('2010-08-31', INTERVAL 1 MONTH)) | +----------------------------------------------------+ | 2010-09-30 | +----------------------------------------------------+ 1 row in set (0.00 sec) There is also a tricky use of the DATE_FORMAT() function to get the first day of a month. You can use it as follows:
SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01'); +---------------------------------------------------------------+ | DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH),'%Y-%m-01') | +---------------------------------------------------------------+ | 2010-07-01 | +---------------------------------------------------------------+ 1 row in set (0.00 sec) Therefore:
SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01') AS FirstDayOfNextMonth, LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) AS LastDayOfNextMonth; +---------------------+--------------------+ | FirstDayOfNextMonth | LastDayOfNextMonth | +---------------------+--------------------+ | 2010-07-01 | 2010-07-31 | +---------------------+--------------------+ 1 row in set (0.00 sec) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
