CODEWITHSUNDEEP
mysqlsql
user5980704
user5980704

Reputation:

Mysql -- Last 30 days

I have a Table with two colums.

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?

Upvotes: 15

Views: 42211

Answers (4)

Faisal
Faisal

Reputation: 4767

SELECT  *
FROM    cc_open_incident_view
WHERE   yourdatetimecolumn BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Also note that CURDATE() returns only the DATE portion of the date, so if you store yourdatetimecolumn 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  *
FROM    cc_open_incident_view
WHERE   yourdatetimecolumn BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

Upvotes: 4

Utsav
Utsav

Reputation: 8093

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 )

Upvotes: 30

Kaushik Patel K K
Kaushik Patel K K

Reputation: 1

select * 
from cc_open_incident_view where DATE >= [strat date] and DATE <= [current date];

Upvotes: 0

incompleteness
incompleteness

Reputation: 270

If the datatype is datetime or timestamp (and you want to check the time):

SELECT * 
FROM yourtable 
WHERE yourdatetimeortimestampcolumn <= NOW() - INTERVAL 1 MONTH;

If your column datatype is date:

SELECT * 
FROM yourtable 
WHERE yourdatecolumn <= CURRENT_DATE() - INTERVAL 1 MONTH;

If your column is a string, you will need to convert it first by doing:

SELECT STR_TO_DATE('5/16/2011 20:14 PM', '%c/%e/%Y %H:%i');

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date

Upvotes: 0

Related Questions