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

Sherpa 161 Reputation points
2020-12-02T03:28:32.667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,854 questions
{count} votes

Accepted answer
  1. Olaf Helper 41,006 Reputation points
    2020-12-02T06:47:02.717+00:00

    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())) 
    )
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful