CODEWITHSUNDEEP
mysql
Meeran Mohideen
Meeran Mohideen

Reputation: 167

Searching records between two date columns

I have a table with below format

--------------------------------------------------------
type   |   requirements   |   from       |     to      |
--------------------------------------------------------
 A          23               2012-12-12      2012-12-24|
--------------------------------------------------------
B           22               2012-12-10      2013-01-14|
--------------------------------------------------------
C           20               2013-11-18      2012-12-10|
--------------------------------------------------------

Now when I search the total requirements for a date 2010-12-23. I should get 

----------------------------
  type   |   requirements   |
  ---------------------------
    A          23           |
  ---------------------------
    B           22          |
-----------------------------

What is the query to get this result? I searched a lot but couldn't find any hint. Please help me.

Upvotes: 0

Views: 43

Answers (2)

andreas
andreas

Reputation: 8004

Why not 

SELECT * FROM TABLE WHERE "from" > "2010-12-23" AND "to" < "2010-12-23"

?

If you want to include the two dates you could use >= and <= or also use BETWEEN like this:

SELECT * FROM TABLE WHERE "2010-12-23" BETWEEN "from" AND "to"

Just FYI, the names "from" and "to" are not ideal as column names, but I guess you just posted it as example.

Upvotes: 1

eggyal
eggyal

Reputation: 125835

Assuming that by '2010-12-23' you meant '2012-12-23', you can filter using MySQL's BETWEEN ... AND ... comparison operator:

SELECT `type`, `requirements`
FROM   `my_table`
WHERE  '2012-12-23' BETWEEN `from` AND `to`

See it on sqlfiddle.

Upvotes: 4

Related Questions