MySQL Filter Query by Date Range

Question

I would like to create a query that filters records in a table using a date range.

The table has a "date" column that follows MM/DD/YYYY format.

I have this existing function that filters the records by "type" automatically, but how can I attach arguments to this function that uses a date range to only show records that fall within the range?

Say, for example, find a record that falls between 04/01/2015 and 04/30/2015.

public function getByType() {

    $query = $this->pdo->prepare("SELECT * FROM `" . $this->table . "` WHERE type='hod'");
    $query->execute();

      if ($query->rowCount() == 0) return null;

}

Thank you!

MySQL stores a DATE type as YYYY-mm-dd

Funk Forty Niner
– Funk Forty Niner

2015-10-21 14:40:24 +00:00
Commented Oct 21, 2015 at 14:40 — Funk Forty Niner
– Funk Forty Niner, Commented Oct 21, 2015 at 14:40

Abhik Chakraborty · Accepted Answer · 2015-10-21 14:40:11Z

3

You are storing dates with wrong data types. Storing dates as varchar string is like inviting evil to your application. You should always store dates with mysql native date data types date,datetime,timestamp etc. with the date as Y-m-d format

However in the current case you can use str_to_date function to get the job done, but in long run you should think about correcting these.

So here how it works

mysql> select str_to_date('04/01/2015','%m/%d/%Y') as d ;
+------------+
| d          |
+------------+
| 2015-04-01 |
+------------+

So the query you could use as

SELECT * FROM table_name 
WHERE 
type='hod' 
and str_to_date(date,'%m/%d/%Y') between str_to_date('04/01/2015','%m/%d/%Y') 
and str_to_date('04/30/2015','%m/%d/%Y')

answered Oct 21, 2015 at 14:40

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cpcdev Over a year ago

Thanks so much! Worked like a charm. I will update my dates in the DB to work with MySQL date type.

Collectives™ on Stack Overflow

MySQL Filter Query by Date Range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related