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

Question

I hope following query will give you the idea what I am looking for-

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 '2014-04-01' AND '2014-04-31')
) AS t1

In my query, you can see the working_date which I given here manually. But, I would not like to do it manually. I would like to pick first day and last day of current month dynamically.

Answer 1

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())

Answer 2

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

Answer 3

You can achieve it these ways ----

/* Current month*/
SELECT DATE_SUB(LAST_DAY(NOW()),INTERVAL DAY(LAST_DAY(NOW()))-1 DAY),CONCAT(LAST_DAY(NOW()),' 23:59:59');

SELECT LAST_DAY(CURDATE()) - INTERVAL DAY(LAST_DAY(CURDATE()))-1 DAY ,CONCAT(LAST_DAY(NOW()),' 23:59:59');

/* previous month*/
SELECT  DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH,'%Y-%m-01 00:00:00'),DATE_FORMAT(LAST_DAY(CURDATE()-INTERVAL 1 MONTH),'%Y-%m-%d 23:59:59');