March 18, 2004 at 7:30 am
I have the following query that is just doing an evaluation of a date, and if True, convert the date to a string for output.
Select
If ExEeTermDate > DATEADD(m, DATEDIFF(m, 0, GETDATE()) -1 ,0)
Begin
Convert(Char(8), DATEADD(m, DATEDIFF(m, 0, ExEeTermDate) + 1, 0), 112) --First day of the next month
End
Else
Begin
Space(8)
End
As TermDate
From KaiserExport
This is part of a bigger query, but I pulled this out to simplify things.
Here is th error I get.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'If'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Convert'.
I have verified that the Convert statement works fine by itself. The If statement NEVER works. I have even tried creating a Var and assigning my date to that, then comparing it in the If statement like this:
If If ExEeTermDate > @MyDate
but it did not make any difference.
I am unable to think of what I have wrong in this situation. Can anyone give my a nudge in the right direction?
Thanks,
Chris
March 18, 2004 at 9:04 am
The problem is that you can't use an IF statement within the select as you have it structured. Try the following, using a CASE statement instead:
Select
case
when ExEeTermDate > DATEADD(m, DATEDIFF(m, 0, GETDATE()) -1 ,0)
then Convert(Char(8), DATEADD(m, DATEDIFF(m, 0, ExEeTermDate) + 1, 0), 112) --First day of the next month
else Space(8)
end
As TermDate
From KaiserExport
I tested this using data from the PUBS database as a substitute and it worked fine.
Hope this helps. I generally find the CASE structure to be much more useful in situations like this than using IF statements.
March 18, 2004 at 9:10 am
OK, I just tried Case and made it work. Thanks.
So what good is the If statement and where can it practically be used?
Thanks,
Chris
March 18, 2004 at 9:15 am
No problem! Glad it worked. The IF statement still has plenty of utility. I think of it primarily as a "control-of-flow" operator. I use it in stored procedures a lot, to control what happens based on a condition:
IF value = x
do something
else
do something else
CASE is better to use for selecting values, as you see. It works nicely in select statements, and you'll probably find yourself using it in WHERE clauses too.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply