CODEWITHSUNDEEP
sql-servert-sqlcase
Greg
Greg

Reputation: 13

How do you use a case statement to make date comparisons (> or <) in T-SQL?

I'm getting an error on the greater than operator in the case statement. What am I missing here?

DECLARE @ODS_LOAN_MAXDATE DATE, @MAX_MAINTENANCE DATE, @MAX_TRANS DATE, @ODS_LOAN_Count int 

SELECT @MAX_MAINTENANCE = MAX(DATE_MAINTENANCE_LAST) FROM FISERV_ODS..LOAN_MASTER_FILE
SELECT @MAX_TRANS = MAX(DATE_TRANS_LAST) FROM FISERV_ODS..LOAN_MASTER_FILE

SELECT CASE @ODS_LOAN_MAXDATE
    WHEN @MAX_MAINTENANCE > @MAX_TRANS
        THEN @MAX_MAINTENANCE
    ELSE @MAX_TRANS 
END 
FROM FISERV_ODS..LOAN_MASTER_FILE

Upvotes: 1

Views: 1522

Answers (1)

aquinas
aquinas

Reputation: 23796

The syntax should be: 

SELECT CASE WHEN @MAX_MAINTENANCE > @MAX_TRANS
        THEN @MAX_MAINTENANCE
    ELSE @MAX_TRANS 
END

Upvotes: 3

Related Questions