user1421214 user1421214 - 2 years ago 98
PHP Question

checking if a date falls in given date range using mysql query

I have these two columns in my database table 'mycodes'


I need to check if a given date falls in valid period of the dates given in mycodes tables.

For example,

id, code_valid_from_date, code_expiry_date, name
1, 2013-08-01, 2013-08-28, 'Code 1'
2, 2013-08-29, 2013-09-20, 'Code 2'
3, 2013-07-01, 2013-07-28, 'Code 3'

I tried this query,

DATEDIFF(NOW(), code_valid_from_date) valid_from_days,
DATEDIFF(code_expiry_date, NOW()) expiry_days_left
FROM mycodes

then in my php code I check both of these DATEDIFF are positive numbers then its a valid code. What if I have to compute this validity in the mysql query itself rather than checking using PHP? How do I do that?

PS: I could also use a BETWEEN in WHERE clause but that will return rows which fall in that given range but I need to list all the records with status of expired or not expired.

Answer Source

Mysql knows to compare dates, so you can use something like:

SELECT (NOW() >= code_valid_from_date AND NOW() <= code_expiry_date) AS valid FROM mycodes
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download