使用mysql查询检查日期是否在给定的日期范围内

Question

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

code_valid_from_date
code_expiry_date

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

For example,

mycodes
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,

SELECT
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 1

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