Case funtion syntax error. 1

[jcoleman]

Can anyone tell me what I'm doing wrong with the following code; I'm getting 'Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'case'.' when I check the syntax.

declare @dayofweek tinyint
declare @tuesday date
set @dayofweek = datepart(dw,getdate())
case @dayofweek
when 1 then @tuesday=dateadd(d,-5,getdate())
when 2 then @tuesday=dateadd(d,-6,getdate())
when 3 then @tuesday=getdate()
when 4 then @tuesday=dateadd(d,-1,getdate())
when 5 then @tuesday=dateadd(d,-2,getdate())
when 6 then @tuesday=dateadd(d,-3,getdate())
when 7 then @tuesday=dateadd(d,-4,getdate())
end

Also, if there's an easier way of finding out what the previous Tuesday's date was, I'm open to suggestions.

Thanks,
JC

 

[donutman]

The reason your code didn't work is because the case statement only returns value. It doesn't execute an assignment statement. The way to write what you wanted is:

[Blue]SET[/Blue] @Tuesday[Gray]=[/Gray][Blue]CASE[/Blue] @dayofweek
   [Blue]WHEN[/Blue] 1 [Blue]THEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]d[Gray],[/Gray][Gray]-[/Gray]5[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]
   [Blue]WHEN[/Blue] 2 [Blue]THEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]d[Gray],[/Gray][Gray]-[/Gray]6[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]
   [Blue]WHEN[/Blue] 3 [Blue]THEN[/Blue] [Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray]
   [Blue]WHEN[/Blue] 4 [Blue]THEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]d[Gray],[/Gray][Gray]-[/Gray]1[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]
   [Blue]WHEN[/Blue] 5 [Blue]THEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]d[Gray],[/Gray][Gray]-[/Gray]2[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]
   [Blue]WHEN[/Blue] 6 [Blue]THEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]d[Gray],[/Gray][Gray]-[/Gray]3[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]
   [Blue]WHEN[/Blue] 7 [Blue]THEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]d[Gray],[/Gray][Gray]-[/Gray]4[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]
   [Blue]END[/Blue]

But a more efficient method is:

[Blue]SET[/Blue] [Black]DateFirst[/Black] 2
[Blue]SELECT[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]d[Gray],[/Gray][Gray]([/Gray]1[Gray]-[/Gray][Fuchsia]DatePart[/Fuchsia][Gray]([/Gray]dw[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray][Gray])[/Gray][Gray]%[/Gray]7[Gray],[/Gray] [Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

I guess you could make it look cleaner by setting Wednesday as the first day of week. That way if today is Tuesday the value of "day of week" will be 7, modulo 7 it is 0. So you add -0 to GetDate() to get right back to Today!

SET DateFirst 3
SELECT DateAdd(d,-DatePart(dw,GetDate())%7, GetDate())

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[yksvaan]

One way, regardsless of what SET DateFirst value is, it goes

SELECT DATEADD(dd,-(7+DATEDIFF(dd,1,getdate())%7)%7,getdate())

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[donutman]

Kewl!

 

[jcoleman]

Thanks, guys it works great now.

 

[yksvaan]

Karl, what does it mean: Kewl

I have a guess and I agree, but ...



Cheers

 

[donutman]

Just a compliment (cool) on a nice technique for which I gave you a star. It's not a real word so don't add it to your vocabulary.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[yksvaan]

Ok, thanks Karl, I got it later how it sounds, it wasn't my first guess though, but now I don't have to explain why I liked to do it that way! I was insprired by "the most gorgeous way" to handle dateparts, what for I gave you a star then.

BTW, the word wouldn't happen to have some other meaning, why it shouln't be used..?

Cheers,
Pete

 

[donutman]

Pete, I assumed that English wasn't your 1st language (only because of the question and yksvaan - certainly not because of your writing skills which appear to be native). Hence a suggestion not to use kewl since it's usually inappropriate to butcher the language.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]