Filteration in where clause: syntax error?

Question

Please help me to sort this query:

WHERE
RECEIPT_DATE BETWEEN( Coalesce(@FROM_DATE,RECEIPT_DATE) AND Coalesce(@TO_DATE,RECEIPT_DATE) )
AND 
OFFICE_ID=Coalesce(@OFFICE_ID,OFFICE_ID)

Error: Incorrect syntax near the keyword 'AND'.

Answer 1

Try this:

WHERE
   RECEIPT_DATE BETWEEN 
      Coalesce(@FROM_DATE, RECEIPT_DATE) AND Coalesce(@TO_DATE, RECEIPT_DATE) 
   AND 
   OFFICE_ID = Coalesce(@OFFICE_ID, OFFICE_ID)

You need to have two dates following the BETWEEN (BETWEEN Date1 AND Date2) and no brackets around that.

Answer 2

Check this query its working fine with no errors

declare @FROM_DATE datetime
declare @TO_DATE datetime
declare @OFFICE_ID int
select * from table1 WHERE
(RECEIPT_DATE BETWEEN  Coalesce(@FROM_DATE,RECEIPT_DATE) AND Coalesce(@TO_DATE,RECEIPT_DATE) )
AND 
OFFICE_ID=Coalesce(@OFFICE_ID,OFFICE_ID)

Mistake in your where clause is

BETWEEN (

because you cannot put ( after BETWEEN.

Answer 3

BETWEEN is a keyword, not a function, so doesn't take parenthesis. Try wrapping the entire logical construct if you are finding the placement of the AND confusing.

WHERE (X BETWEEN A AND B) AND (Y = C)