CODEWITHSUNDEEP
sql-server
Trincula
Trincula

Reputation: 59

Its saying error when I tried using CAST and CASE WHEN together

I'm having problem for Casting CurrRunDate by comparing the values. 

SELECT
    s.Cid, 
    s.MobileNo, 
    AvailableBalance = (SELECT dbo.GetAvailBalAmt(acc,0)/100 FROM SMSCIF), 
    b.CurrRunDate,
    s.CreatedDate,
    s.ExpiryDate,       
    CAST (CASE WHEN
                  b.CurrRundate < s.ExpiryDate THEN '00' 
                  ELSE '01' 
          END) AS RenewStatus
FROM
    SMSCIF s
LEFT JOIN 
    BRPARMS b ON s.br = b.br
WHERE 
    (CAST(s.CreatedDate AS DATE) = b.CurrRundate AND s.status = '00') 
    OR Cast(s.ExpiryDate As Date) = b.CurrRundate

Error Message : Incorrect syntax near 'Cast', expected 'AS'.

Upvotes: 1

Views: 57

Answers (2)

Bohemian
Bohemian

Reputation: 425418

Assigning an alias to an expression doesn't need CAST; remove CAST:

CASE
    WHEN b.CurrRundate < s.ExpiryDate THEN '00' 
    ELSE '01' 
END AS RenewStatus

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13247

You need CAST( (CASE expression) AS VARCHAR(2)), your current query has a syntax error.

Change it to the query below will work:

 CAST ((CASE WHEN b.CurrRundate < s.ExpiryDate THEN '00' 
            ELSE '01' END) 
 AS VARCHAR(2)) AS RenewStatus,

Upvotes: 1

Related Questions