Hi there,

I am trying to select records which have start and end dates that span or include any given month.

I came up with this query but for some reason it isn't giving me the results I expected:

SELECT * FROM Orders WHERE stationid = '26' AND productid = '1' AND ((now() BETWEEN startdate AND enddate) OR ('11' BETWEEN MONTH(startdate) AND MONTH(enddate))) ORDER BY productid

'startdate' and 'enddate' are stored in the DB as timestamp(14) fields.

In this particular case, month = 11 (Nov) but it still retrieves orders with a startdate = 20050901000000 (Sept) and enddate = 20051005000000 (Oct).

I have been beating myself over the head trying to figure out how to pull out records if they happen in a specific month, and I thought I finally had, until I realized this was happening.

Any help would be appreciated. Thanks.

If NOW() returns the timestamp of now (around 2005-09-28), your
'now() BETWEEN startdate AND enddate' subclause, which is ORed with your month selection logic, is true.

--
felix