case when - incorrect syntax near

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

case when - incorrect syntax near

Author

Topic

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2008-09-19 : 09:26:24

I get "incorrect syntax near =" when I try to do a when case in a where clause...

where case when {fn DAYOFWEEK( {fn current_date()} ) } = 2 then
del_date = dateadd ("d", -3, {fn current_date()} )
case else
del_date = dateadd ("d", -1, {fn current_date()} )
end

or

select datepart("dw",getdate() ) = 5

or

where case when datepart("dw",getdate() ) = 2 then
del_date = dateadd ("d", -3, {fn current_date()} )
case else
del_date = dateadd ("d", -1, {fn current_date()} )
end

I think I read that this might be because I am doing an I/O and comparing to a variable. If Monday, I want to select data for the previous Friday, else the previous day.

How do I do this in the same query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 09:44:45

it should be

where del_date =case when {fn DAYOFWEEK( {fn current_date()} ) } = 2 then
 dateadd ("d", -3, {fn current_date()} ) 
else
 dateadd ("d", -1, {fn current_date()} ) 
end 
or datepart("dw",getdate() ) = 5

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2008-09-29 : 07:30:51

Thanks, I will try it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 07:36:16

Why are you using ODBC calls? It comes with an overhead...
Also, remember that DATEPART for weekday and week is dependant on SET DATEFIRST setting.

SELECT	CASE DATENAME(WEEKDAY, GETDATE())
		WHEN 'Monday' THEN -3
		WHEN 'Sunday' THEN -2
		ELSE -1
	END + DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

E 12°55'05.63"
N 56°04'39.26"

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2008-10-07 : 09:42:08

Thanks,

I will make note of it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources