SQL BETWEEN 2 date values HELP

Question

SELECT * FROM myDateTable WHERE date_Start OR date_end BETWEEN DateValue('" & CoverMonth_start & "') AND DateValue('" & CoverMonth_end & "')"

TheCoverMonth start and end dates are looping from January to December. This query is suppost to select only the records WHERE date_Start OR date_end BETWEEN DateValue... But this query is selecting all the records in the database.

Answer 1

This is because you are doing date_Start OR...., this is selecting all the records that has ANY value in date_Start and date_End is in the given values range.

What you should write is this:

SELECT * FROM myDateTable WHERE
    date_Start BETWEEN DateValue('" & CoverMonth_start & "') AND DateValue('" & CoverMonth_end & "')"
    OR 
    date_end BETWEEN DateValue('" & CoverMonth_start & "') AND DateValue('" & CoverMonth_end & "')"

Answer 2

SELECT * FROM myDateTable WHERE date_Start between date1 and date2 OR date_end BETWEEN date1 and date2

Answer 3

Your query isn't doing what you think it is.

The OR is your problem - the moment the date_start is populated, that record will be returned.

This is probably the query that you want:

 SELECT * FROM myDateTable 
 WHERE (date_Start 
    BETWEEN DateValue('" & CoverMonth_start & "') 
    AND DateValue('" & CoverMonth_end & "')")
 OR (date_end 
    BETWEEN DateValue('" & CoverMonth_start & "') 
    AND DateValue('" & CoverMonth_end & "')")

Answer 4

SELECT * 
FROM myDateTable
WHERE (date_start BETWEEN .... AND ....)
OR
(date_end BETWEEN .... AND ....)

Answer 5

OR has lower precedence than BETWEEN.

WHERE date_Start OR date_end BETWEEN a AND b translates to WHERE CAST(date_Start AS BOOLEAN) = true OR ( date_end BETWEEN a AND b)

what you want is... oh see other answers (an easy question, isnt'it?)