CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SQL nested if else if Syntax error

declare @timeid int

if(datename(Dw,getdate())='Monday')
begin
set @timeid=3
end
Else if(datename(Dw,getdate())='Sunday' or datename(Dw,getdate())='Saturday')
begin
  set @timeId=2
end

ELSE   -- for Tuesday to Friday

begin
 if(convert(varchar(11),getdate(),108)<='08:30:00')
  begin
   set @timeId=1
  end
 else
  begin
   set @timeId=0
  end
end

select @timeid

Error is being thrown:

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'Else'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '@timeid'.

Please help.

Upvotes: 0

Views: 7283

Answers (2)

Dale M
Dale M

Reputation: 2473

OK, I'm pretty sure your syntax error is the varchar(11) should be varchar.

Notwithstanding, this is not good use of SQL; first, you use getdate() 3 times - each time will be different (by milliseconds) this could mean ticking over from Friday to Saturday between the first and last call, or from before 08:30 to after 08:30.

Try this:

declare @timeid int
declare @nowtime datetime

select @nowtime=getdate()

select @timeid = CASE datename(Dw,@nowtime)
                   WHEN 'Monday' THEN 3
                   WHEN 'Sunday' THEN 2
                   WHEN 'Saturday' THEN 2
                   ELSE
                     CASE 
                       WHEN convert(varchar,getdate(),108)<='08:30:00' THEN 1
                       ELSE 0
                     END
                 END

Upvotes: 3

harpo
harpo

Reputation: 43168

@DaleM beat me to it, but yeah, here's another rewrite for clarity:

DECLARE @DOW_SUNDAY int
DECLARE @DOW_MONDAY int
DECLARE @DOW_SATURDAY int

SET @DOW_SUNDAY = 1
SET @DOW_MONDAY = 2
SET @DOW_SATURDAY = 7

DECLARE @now datetime
DECLARE @day_of_week int

SET @now = GETDATE()
SET @day_of_week = DATEPART(DW, @now)

SELECT CASE WHEN @day_of_week = @DOW_MONDAY
            THEN 3
            WHEN @day_of_week IN (@DOW_SATURDAY, @DOW_SUNDAY)
            THEN 2
            WHEN CONVERT(varchar, @now, 108) <= '08:30:00'
            THEN 1
            ELSE 0
        END

Upvotes: 2

Related Questions