Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2004-09-23 : 09:23:47
|
For some reason I always seem to have trouble with IF and CASE statements in SQL, here is another. This has come to me from elsewhere so you can criticise all you like and you won't hurt my feelings - In fact I would appreciate all contstructive (and some non constructive) comments.the statement is:- SELECT ...,w.wl_SuspensionReason, IF((w.SuspensionStart <= GETDATE()) And (w.SuspensionEnd > GETDATE())) THEN 'YES'ELSE 'NO' END AS InSuspension, ...FROM WLList w What is meant to be happening is this. If todays date is between (or matching) SuspensionStart and SuspensionEnd then InSuspension should be 'YES' Otherwise it should be 'NO'. SuspensionStart and SuspensionEnd are both dates.I have three problems, firstly the syntax is wrong on the IF statement - I've tried putting BEGIN and END's in but can't quite get it right, I even tried a CASE statement but that seemed completely over the top and refused to work anyway, secondly I suspect that the date comparisons should be performed differently (using DATEDIFF maybe) but would appreciate thoughts on the matter. Finally, This is to be called from ACCESS 97 or EXCEL 97 (remember them!), would I be better putting it into a sproc or a view on my SQL Server 7?Thanks in advancesteveSteve no function beer well without |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-09-23 : 09:33:48
|
| [code]SELECT ...,w.wl_SuspensionReason, CASE WHEN (w.SuspensionStart <= dateadd(dd, datediff(dd,0,getdate()), 0) And w.SuspensionEnd > dateadd(dd, datediff(dd,0,getdate()), 0)) THEN 'YES'ELSE 'NO' END AS InSuspension, ...FROM WLList w[/code]You would be much better off putting this into a stored proc as i'm not sure this will work in Access...the dateadd and datediff around getdate() will give you midnight of the current date as you will get the time portion otherwise and it seems you really don't want the time... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-23 : 10:38:24
|
| Being an old chestnut I'd love some new flames ...Kristen |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-23 : 11:11:26
|
| "IF" is a control of flow STATEMENT, which cannot be used in a SELECT. It's used to determine which statement to execute next in a T-SQL batch, depending on conditions. Use BEGIN/END to execute more than 1 statement as by default, IF..ELSE will only skip/execute 1 single statement."CASE", despite its appearence and lack of parenthesis, it basically a FUNCTION which returns a single value. SInce it's a function, it can be used in SELECT statements.- Jeff |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-09-23 : 11:34:35
|
| Thanks Jeff, as you may have realised that hadn't actually twiggedKristen - You and me bothsteveSteve no function beer well without |
|
|
|
|
|
|
|
|
Old chestnuts and new flames