I have a Table with two colums.
The first column is a date stored as: (example): 01/01/2015 00:00:00
The seconds column is a number : as (example) : 500
I have written an SQL Statement which looks like this:
select *
from cc_open_incident_view
WHERE (DATE =(NOW() - INTERVAL 1 MONTH))
If I execute this statement it doesn't retrieve any data and I can't find the mistake
I want only the data for the last month as a result of the query (last 30 days).
Would appreciate any help..
Edit as per OP comments:
Date is saved as date and it's working, but the column date in my table has stored the dates for the next 5 years and it shows the data like: 09.03.2016 (which is tomorrow) too. Is there any way to only show the date from 30 days back till today?
解决方案
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 )