Validate a date range within MySQL query

Question

(This question may seem easy or kind of noobish, by that I pardon my ignorance.)

I used PDO query to use SELECT then fetch some values, it comes to a point that I need to fetch only some entries that within its start date and end date.

My database

+----------+-----------------+----------------------+--------------------+
| id (INT) | title (VARCHAR) | start_date (VARCHAR) | end_date (VARCHAR) |
+----------+-----------------+----------------------+--------------------+
|    1     |     buddy       |     2012-11-26       |     2012-11-30     |
|    2     |     metro       |     2012-12-05       |     2012-12-20     |
|    3     |     justin      |     2012-11-28       |     2012-12-01     |
+----------+-----------------+----------------------+--------------------+

My query is as follows:

$query = "SELECT title, start_date, end_date FROM debts WHERE start_date >= CURDATE() AND end_date >= CURDATE()";

What I want to achieve is whenever the start_date is today or greater but not exceeding the end_date it will be valid. This will return the row for id 1, however if I change the start_date to 2012-11-25, it will fail due to the first condition on AND. I'm really confuse on this since I am new to this, is there any built-in function to handle this kind of situation?

start_date >= CURDATE() AND end_date >= CURDATE().Sorry, I didn't understand that part — harry, Nov 26 '12 at 10:58
use date_format() when comparing dates, http://www.w3schools.com/sql/func_date_format.asp — Nilam Doctor, Nov 26 '12 at 10:59
What do you mean with "it will fail"...do you get an error? If yes, which one? — Florian Gl, Nov 26 '12 at 11:00
Pardon, no error thrown, it works but not what I wanted to be. I edited my post for clarification. — fishcracker, Nov 26 '12 at 11:03

score 1 · Accepted Answer · answered Nov 26 '12 at 11:02

1

This is better:

SELECT title, start_date, end_date FROM debts WHERE date(now()) BETWEEN start_date AND end_date

answered Nov 26 '12 at 11:02

Stanislav

2,683
2
18
14

Thus between means it includes the date specified on `start_date` / `end_date`? – fishcracker Nov 26 '12 at 11:05
Yes, and it uses indexes on start_date and end_date fieldsRead more here http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between – Stanislav Nov 26 '12 at 11:08

score 0 · Answer 2 · answered Nov 26 '12 at 10:57

0

SELECT title, start_date, end_date FROM debts
WHERE start_date >= CURDATE() AND end_date <= CURDATE()

answered Nov 26 '12 at 10:57

AnandPhadke

13,160
5
26
33

Validate a date range within MySQL query

2 Answers2