Get the First and Last Date of Next Month in MySQL

Get the first and last date of next month in MySQL

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

MySQL: Select with first and last day of previous month

in MYSQL you can try the below

First day of Previous Month

select last_day(curdate() - interval 2 month) + interval 1 day

Last day of Previous Month

select last_day(curdate() - interval 1 month)

First day of Current Month

select last_day(curdate() - interval 1 month) + interval 1 day

Last day of Current Month

select last_day(curdate())

Get First & Last Day of Month from Year & Month variables

SET @Months = 3;
SET @Years = 2014;
SELECT DATE_FORMAT(DATE(concat(@Years, '-', @Months, '-01')), '%Y-%m-%d');
SELECT LAST_DAY(DATE(concat(@Years, '-', @Months, '-01')));

'Year' and 'Month' are reserved function names in MySQL. So I would suggest renaming it to avoid the confusion.

MySQL first day and last day of current and previous month from date (no timestamp)

You can use LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY,which will subtract one month from now and by by adding 1 day in LAST_DAY of previous month will give you the first day of current month

SELECT SUM(t1.hours) AS totalhours FROM
(
SELECT (time_to_sec(timediff(time_out, time_in)) / 3600) AS hours FROM bb_work_log
WHERE user_id = 6
AND (working_date BETWEEN LAST_DAY(NOW() - INTERVAL 1 MONTH)
AND LAST_DAY(NOW()))
) AS t1

LAST_DAY(NOW() - INTERVAL 1 MONTH) this will give you the last day of
previous month

First/Last day of Month Fiddle Demo

How do I select between the 1st day of the current month and current day in MySQL?

select * from table_name 
where (date between DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 30 day), interval 1 day) AND CURDATE() )

Or better :

select * from table_name 
where (date between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )

Get the first and last working day date of some month in MySQL

E.g.:

SELECT MIN(dt),MAX(dt) FROM calendar WHERE DAYOFWEEK (dt) NOT IN(1,7) AND dt BETWEEN '2016-01-01' AND '2016-01-31';

Need to get dates between last April and next March in MySQL

One way could be

SELECT  .....
WHERE somedate BETWEEN
CONCAT(IF(MONTH( CURDATE() ) < 4, YEAR( CURDATE() ) - 1, YEAR( CURDATE() )), '-04-01')
AND
CONCAT(IF(MONTH( CURDATE() ) < 4, YEAR( CURDATE() ), YEAR( CURDATE() )+1), '-03-31');

How can I select the first day of a month in SQL?

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth


Related Topics



Leave a reply



Submit