how to set variable using CASE?

22 Jul 2006 11:31 PM

Phill

How do I set a variable using CASE. This is my mess:
IF DATENAME(dw,CAST(@strFromDate AS DATE)) = 'Monday' SET @i=6

        --WHEN 'Tuesday' THEN
        --    5
        --WHEN 'Wednesday' THEN
        --    4
        --WHEN 'Thursday' THEN
        --    3
        --WHEN 'Friday' THEN
        --    2
        --WHEN 'Saturday' THEN
        --    1
        --WHEN 'Sunday' THEN
        --    0
    --END

A lot is commented out in frustration.

22 Jul 2006 11:49 PM

Tom Cooper

Set @i = Case DATENAME(dw,CAST(@strFromDate AS DATETIME))
WHEN 'Monday' THEN 6
WHEN 'Tuesday' THEN 5
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 3
WHEN 'Friday' THEN 2
WHEN 'Saturday' THEN 1
WHEN 'Sunday' THEN 0
END

Tom

Show quote

"Phill" <Ph***@discussions.microsoft.com> wrote in message
news:E81532A7-A9D5-48E7-AF7C-4359F8228218@microsoft.com...
> How do I set a variable using CASE. This is my mess:
> IF DATENAME(dw,CAST(@strFromDate AS DATE)) = 'Monday' SET @i=6
>
> --WHEN 'Tuesday' THEN
> -- 5
> --WHEN 'Wednesday' THEN
> -- 4
> --WHEN 'Thursday' THEN
> -- 3
> --WHEN 'Friday' THEN
> -- 2
> --WHEN 'Saturday' THEN
> -- 1
> --WHEN 'Sunday' THEN
> -- 0
> --END
>
> A lot is commented out in frustration.

23 Jul 2006 12:02 AM

Omnibuzz

If this is your requirement, you don't need the case statement...

try this

SET DATEFIRST 7
SELECT @i = (8 - (DATEPART(dw,CAST(@strFromDate AS DATETIME))))%7

Hope this helps.

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

23 Jul 2006 1:49 PM

Phill

Thanks guys.

Show quote

"Omnibuzz" wrote:

> If this is your requirement, you don't need the case statement...
>
> try this
>
> SET DATEFIRST 7
> SELECT @i = (8 - (DATEPART(dw,CAST(@strFromDate AS DATETIME))))%7
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>
>