How to use different date comparisons based upon a value in sql

Question

I have to write an update query. If the special_member account is not canceled then in the where clause I have to use this condition by adding a grace period of 15 to the expiry date and compare it today's date: Convert(date,MEMBER_EXPIRY_DATE + 15) >= Convert(date,GETDATE()). If the membership is canceled then I have to compare with actual expiry date with today's date. This is my full query:

UPDATE SPECIAL_MEMBER SET SAVINGS_PERCENT = 10, ORDER_COUNT = 1
WHERE SPECIAL_MEMBER = '4382' AND CASE WHEN (CANCELLED = 0) THEN
Convert(date,MEMBER_EXPIRY_DATE + 15) >= Convert(date,GETDATE())
ELSE (Convert(date,MEMBER_EXPIRY_DATE) >= Convert(date,GETDATE())) END

When I execute it I am getting Incorrect syntax near '>'

Thanks

Answer 1

You can not use a CASE condition in this way in a WHERE clause, you need at least a real compare.

You can re-write the query with boolean logic like this

UPDATE SPECIAL_MEMBER 
SET SAVINGS_PERCENT = 10, ORDER_COUNT = 1 
WHERE SPECIAL_MEMBER = '4382' AND 
((CANCELLED = 0 AND Convert(date,MEMBER_EXPIRY_DATE + 15) >= Convert(date,GETDATE())
 OR (CANCELLED <> 0 AND Convert(date,MEMBER_EXPIRY_DATE) >= Convert(date,GETDATE())) 
)