CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
DavidStein
DavidStein

Reputation: 3179

Set Variable Values

I'm sure this is something simple, but I can't seem to figure it out. Why doesn't this code work? 

DECLARE @FirstSaturday DATETIME
DECLARE @ENDDATE DATETIME

SELECT @FirstSaturday = min(RED1.DATE)
FROM REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED1
WHERE Period = 9 AND year = 2009
SELECT CASE
WHEN getdate() < @FirstSaturday
THEN  set @ENDDATE = getdate()
ELSE  SET @enddate = @FirstSaturday


END

I simply want the value of the earlier date, today or the end of the period, to be assigned to @enddate.

Can someone point me in the right direction? I am using SQL 2000.

Upvotes: 1

Views: 254

Answers (3)

AnthonyWJones
AnthonyWJones

Reputation: 189457

The SELECT CASE just doesn make any sense, try:-

DECLARE @FirstSaturday DATETIME
DECLARE @ENDDATE DATETIME

SELECT @FirstSaturday = min(RED1.DATE)
FROM REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED1
WHERE Period = 9 AND year = 2009

SELECT @ENDDATE = CASE
WHEN getdate() < @FirstSaturday
THEN  getdate()
ELSE  @FirstSaturday
END

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294277

SELECT @enddate = CASE
WHEN getdate() < @FirstSaturday THEN  getdate()
ELSE  @FirstSaturday
END

CASE in T-SQL is an expression (like ?: in C), not a flow control branch like IF.

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146499

Add the keyword 'End' to the end of the Case statement

 DECLARE @FirstSaturday DATETIME
 DECLARE @ENDDATE DATETIME
 SELECT @FirstSaturday = min(RED1.DATE)
 FROM REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED1
 WHERE Period = 9 
     AND year = 2009

 SELECT @ENDDATE = 
    Case
      WHEN getdate() < @FirstSaturday
      THEN  getdate()
      ELSE  @FirstSaturday 
    End

Upvotes: 1

Related Questions