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

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.

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

SELECT (NOW() BETWEEN code_valid_from_date AND code_expiry_date) AS valid FROM mycodes

This is my code to find if a date range falls within another date range. I'm sure you could modify my choosing the same date as your arrival and departure date.

Example that works for me and my queries:

SET @PerBeg = ’2010-01-01’;
  SET @PerEnd = ’2010-01-31’;

  SELECT  
      Reservation.ArrivalDate AS ArrivalDate, 
      Reservation.DepartureDate AS DepartureDate, 
  . . . 

  WHERE

  fBoolActyInPeriod(ArrivalDate,DepartureDate,@PerBeg,@PerEnd)

using these functions

  FUNCTION fBoolActyInPeriod contains this code
  RETURN IF (fBoolFirstDateEarlier(ActyEnd, PerBeg) OR fBoolFirstDateEarlier(PerEnd, ActyBeg),0, 1)

  FUNCTION fBoolFirstDateEarlier contains this code
  RETURN IF (DATEDIFF(first, second) < 0, 1, 0)