Ms Sql Server - Assigning A Date To A Variable

Declare procursor CURSOR FOR SELECT CLAIMNO from RPT_CLAIM_MD_COMBO DECLARE @myyear Integer Declare @provar varchar(22) open procursor fetch next from procursor into @provar WH

Solution 1:

You can't assign a variable like that. Besides, what's the point? Where are you using that variable at? Just remove the set @MYYEAR = and you should be fine. If you really want to assign that variable with that logic, do it outside of your main SELECT statement.

declare @MYYEAR int
set @MYYEAR = 
(SELECT  
    CASE
        WHEN CONVERT(INTEGER,BTHDAT) = 0 THEN 0
        WHEN  datepart(DY,convert(date, BTHDAT)) > datepart(DY,'2015/07/01') THEN DATEDIFF(YEAR, convert(date, BTHDAT),'2015/07/01') - 1
        ELSE DATEDIFF(YEAR,convert(date, BTHDAT),'2015/07/01')  
    END
FROM SomeTable)

SELECT  
    SERVICE_GROUP, 
    SERVICE_CATEGORY,
    @MYYEAR
FROM
    SomeOtherTable