If Else Statment Syntax Error

Question

If Else Statment Syntax Error

LOOKUP_BI-756009

SSCertifiable

Points: 7286
More actions
February 16, 2010 at 2:40 pm

#230056

Can someone tell me why am I getting error mssg as following
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'IF'.
DECLARE @minutes INT, @word VARCHAR(255),@hours INT, @min-2 INT, @second INT
SET @minutes = ABS(DATEDIFF(MINUTE,'10/16/2009 11:36:47 AM','10/17/2009 4:43:55 PM'))
IF @minutes = 0
SET @word = '00'
ELSE
BEGIN
SET @word = ''
IF @minutes >= (24*60)
set @hours=(RTRIM(@minutes/(24*60))
IF LEN(@hours) < 2
SET @word = @word + '0' + @hours+' :'
IF LEN(@hours) >= 2
SET @word = @word + @hours+' :'
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Did you actually try to figure out what the error message is telling you?

If you can't find anything wrong after the if statement in line 10 the reason might be the error is before the statement. 😉

Strong hint: How many pairs of brackets do you have in your statement "set @hours=(RTRIM(@minutes/(24*60))"

The next strong hint would be given by SSMS by highlighting the corresponding parts of a pair of brackets...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 2

You have a problem in this line:

set @hours=(RTRIM(@minutes/(24*60))

Three open-parens, two closes.

Those can be a pain to spot by eye.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

LOOKUP_BI-756009 (2/16/2010)
Can someone tell me why am I getting error mssg as following
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'IF'.
DECLARE @minutes INT, @word VARCHAR(255),@hours INT, @min-2 INT, @second INT
SET @minutes = ABS(DATEDIFF(MINUTE,'10/16/2009 11:36:47 AM','10/17/2009 4:43:55 PM'))
IF @minutes = 0
SET @word = '00'
ELSE
BEGIN
SET @word = ''
IF @minutes >= (24*60)
set @hours=(RTRIM(@minutes/(24*60))
IF LEN(@hours) < 2
SET @word = @word + '0' + @hours+' :'
IF LEN(@hours) >= 2
SET @word = @word + @hours+' :'
END

Too much work and guaranteed to be RBAR that way... you're forcing yourself to use a UDF even if you want to do it on a whole table. Try this...

DECLARE @StartDate DATETIME,

@EndDate DATETIME

SELECT @StartDate = '10/16/2009 11:36:47 AM',

@EndDate = '10/17/2009 4:43:55 PM'

SELECT CONVERT(VARCHAR(10),DATEDIFF(hh,0,@EndDate-@StartDate)) --Converts the hours

+ RIGHT(CONVERT(CHAR(8),@EndDate-@StartDate,108),6) --Converts mins & secs

AS [HHHHH:MM:SS]

Neat thing about this is, you don't need a UDF to do a whole table's worth...

SELECT CONVERT(VARCHAR(10),DATEDIFF(hh,0,EndDateCol-StartDateCol)) --Converts the hours

+ RIGHT(CONVERT(CHAR(8),EndDateCol-StartDateCol,108),6) --Converts mins & secs

AS [HHHHH:MM:SS]

FROM dbo.YourTable

With a little effort on your part, you can turn it into a deterministic computed column in your table so you don't have to worry about writing the code correctly each time.

My big question would be... why aren't you doing this type of formatting in the GUI (assuming that you have a GUI)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)