Between date columns

[sw9]

hi guys,

 

i've read up on the mysql date functions, but i'm not quite clear on how to do this query. I have a current datestamp called 'mydate' in 'Y-m-d H:i:s' format. Then I have a mySQL table that has a 'startdate' and 'enddate' columns. I need to select the record where 'mydate' is between the startdate and enddate columns.

 

For example, let's say 'mydate' = '2010-02-25 09:36:05'. Then in mytable I have a record which has a startdate field set at '2010-02-25 09:00:00' and it has an enddate field set at '2010-02-25 10:00:00'. I want that record to get retrieved because my date is between those two fields.

 

I know how to do betweens on a single column, but I can't figure out how to do it like I need. any help?

[DavidAM]

Try this:

 

WHERE '2010-02-25 09:36:05' BETWEEN startdate AND enddate

 

[sw9]

Duh. Sorry, I was not doing this right because I wasn't nesting my where clause correctly. All in all it just came to:

select DISTINCT cs.nid from content_type_showing cs 
WHERE cs.field_channel_value = '11' 
AND 
('$mytime' BETWEEN cs.field_showing_airtime_value AND cs.field_showing_airtime_value2 OR cs.field_showing_airtime_value >= '$mytime') 
order by cs.field_showing_airtime_value 
LIMIT 6