CODEWITHSUNDEEP
mysql
shamon shamsudeen
shamon shamsudeen

Reputation: 5858

Mysql:selects all rows with a starting date value from within the last 30 days

Columns in my table

1)id

2) name

3) starting_time.

The date is stored in starting_time field as a normal string and the format is dd-mm-yyyy

I am trying to get records in which the starting_time is not more than 30 days from the current date.

Here is the query I have tried so far

SELECT *  FROM `agency_courses` 
        WHERE  DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= STR_TO_DATE(agency_courses.starting_time, '%d-%m-%Y')

But it returns even if the starting_time more than 30 days from the current date.

UPDATE

I just tried this but returns 0 rows

SELECT COUNT(*) FROM `agency_courses` 
        WHERE   STR_TO_DATE(agency_courses.starting_time, '%d-%m-%Y') <= (NOW() - INTERVAL 30 DAY)

SAMPLE DATA

id name starting_time
1  test1 26-04-2019
2  test2 02-03-2019
3  test3 08-03-2019

Expected result

    2  test2 02-03-2019
    3  test3 08-03-2019

Upvotes: 1

Views: 26

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

You could try using 

select COUNT(*) 
FROM `agency_courses` 
WHERE str_to_date(agency_courses.starting_time, '%d-%m-%Y')
        BETWEEN NOW() 
         AND DATE_SUB(STR_TO_DATE('26-04-2019', '%d-%m-%Y'), INTERVAL 30 DAY)

but Looking to your sample seems you want the value between now() and now() + 30 day 

select COUNT(*) 
FROM `agency_courses` 
WHERE str_to_date(agency_courses.starting_time , '%d-%m-%Y')
    BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

The Compare is wrong. You want only see rows where OLDER than currdate - interval 30 Day. So change your query to:

SELECT COUNT(*) FROM `agency_courses` 
        WHERE  STR_TO_DATE(agency_courses.starting_time, '%d-%m-%Y') >= (NOW() - INTERVAL 30 DAY);

Upvotes: 0

Related Questions