Mysql Query - Records Between Today and Last 30 Days

MySQL Query - Records between Today and Last 30 Days

You need to apply DATE_FORMAT in the SELECT clause, not the WHERE clause:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Also note that CURDATE() returns only the DATE portion of the date, so if you store create_date as a DATETIME with the time portion filled, this query will not select the today's records.

In this case, you'll need to use NOW instead:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

Mysql -- Last 30 days

Edit: Changed query as per OP

select * 
from cc_open_incident_view
WHERE date between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE()

Previous Answer:

If date is saved as date then use this

select * 
from cc_open_incident_view
WHERE date >= (CURDATE() - INTERVAL 1 MONTH )

If date is saved as string then use this (assuming it is in dd/mm/yyyy ...

select * 
from cc_open_incident_view
WHERE STR_TO_DATE(date ,''%d/%m/%y %h:%i:%s')>= (CURDATE() - INTERVAL 1 MONTH )

how to select last 30 days mysql record and group by each day

Save your date as Y-m-d and run the SQL below.

SELECT * FROM your_table WHERE token = 'vitC' AND dateview BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW() GROUP BY dateview

How to get records from last Month (not last 30 days) in mysql query

This is the query of getting all rows in a table belonging to last month in mysql:

SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

An alternative is:

SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL
1 MONTH)

How to select last 30 days dates in MySQL?

I hacked this together from someone else's code, but it seems to work:

SELECT DATE_FORMAT(m1, '%d %b %Y')
FROM (
SELECT SUBDATE( NOW() , INTERVAL 30 DAY) + INTERVAL m DAY AS m1
FROM (
select @rownum:=@rownum+1 as m from
(select 1 union select 2 union select 3 union select 4) t1,
(select 1 union select 2 union select 3 union select 4) t2,
(select 1 union select 2 union select 3 union select 4) t3,
(select 1 union select 2 union select 3 union select 4) t4,
(select @rownum:=-1) t0
) d1
) d2 
WHERE m1 <= now()
ORDER BY m1

The original code by valex is here:

How to get a list of months between two dates in mysql

---

Related Topics