On Apr 17, 5:52 pm, ML <M...@discussions.microsoft.com> wrote:
> Well... almost... :)
> There should be only one 1. ;)
>
> SELECT ...,
> [DayOfWeek],
> CASE [DayOfWeek]
> WHEN 1 THEN 'Monday'
> WHEN 2 THEN 'Tuesday'
> WHEN 3 THEN 'Wednesday'
> WHEN 4 THEN 'Thursday'
> WHEN 5 THEN 'Friday'
> WHEN 6 THEN 'Saturday'
> WHEN 7 THEN 'Sunday'
> ELSE 'Unknown'
> END AS DescriptionOfDayOfWeek
> FROM T1;
>
> ML
>
> ---http://milambda.blogspot.com/

On Apr 17, 6:07 pm, SQL Menace <denis.g...@gmail.com> wrote:
> On Apr 17, 5:52 pm, ML <M...@discussions.microsoft.com> wrote:
>
>
>
>
>
> > Well... almost... :)
> > There should be only one 1. ;)
>
> > SELECT ...,
> > [DayOfWeek],
> > CASE [DayOfWeek]
> > WHEN 1 THEN 'Monday'
> > WHEN 2 THEN 'Tuesday'
> > WHEN 3 THEN 'Wednesday'
> > WHEN 4 THEN 'Thursday'
> > WHEN 5 THEN 'Friday'
> > WHEN 6 THEN 'Saturday'
> > WHEN 7 THEN 'Sunday'
> > ELSE 'Unknown'
> > END AS DescriptionOfDayOfWeek
> > FROM T1;
>
> > ML
>
> > ---http://milambda.blogspot.com/
>
> of course a CASE is not needed in this case ;-)
>
> select datename(dw,DayOfWeek)
>
> this assumes your data is clean and not junk like this: select
> datename(dw,1), datename(dw,666), datename(dw,-1)
>
> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted text -
>
> - Show quoted text -

<ten....@dnartreb.noraa> wrote:
> > CASE [DayOfWeek]
> > WHEN BETWEEN 1 and 7
>
> You can't use WHEN before the comparison target when using a range, only
> when testing for equality.
>
> SELECT
> CASE 5
> WHEN BETWEEN 1 and 7 THEN DATENAME(dw,5)
> ELSE 'Unknown'
> END AS DescriptionOfDayOfWeek
>
> Msg 156, Level 15, State 1, Line 3
> Incorrect syntax near the keyword 'BETWEEN'.
>
> Needs to be:
>
> SELECT
> CASE WHEN 5 BETWEEN 1 and 7 THEN DATENAME(dw,5)
> ELSE 'Unknown'
> END AS DescriptionOfDayOfWeek
>
> Of course, DATENAME(dw, 5) is like saying DATENAME(dw, '1900-01-06')...
>
> (Since 5 is interpreted as a number of days offset from 0, not a number on
> its own.)
>
> What day is today? Is it Thursday or Tuesday?
>
> DECLARE @dp INT;
> SELECT @dp = DATEPART(DW, GETDATE());
> SELECT DATENAME(DW, @dp); -- Thursday
> SELECT DATENAME(DW, GETDATE()); -- Tuesday

"Rico" <me@you.com> wrote in message news:41bVh.6137$j%5.1809@edtnps90...
> Hello,
>
> For anyone that's a VB programmer out there, I'm looking for the evivalent
> of a Choose statemetn in SQL Server. For those that aren't VB guys, a
> choose statement will return a value, based on the numeric value of the
> input. for instance;
>
> Choose(InputValue,[Return if InputValue=1],[Return if
> InputValue=2],[Return if InputValue=3],[Return if InputValue=4],...etc)
>
> Example;
> Choose(DayOfWeek,"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
>
> Any direction is greatly appreciated.
>
> Thanks!
> Rick
>
>

"Itzik Ben-Gan" <itzik@REMOVETHIS.solidqualitylearning.com> wrote in message
news:uTuHnmTgHHA.1244@TK2MSFTNGP04.phx.gbl...
> Rico,
>
> You can use a CASE expression for this purpose:
>
> SELECT ...,
> [DayOfWeek],
> CASE [DayOfWeek]
> WHEN 1 THEN 'Monday'
> WHEN 1 THEN 'Tuesday'
> WHEN 1 THEN 'Wednesday'
> WHEN 1 THEN 'Thursday'
> WHEN 1 THEN 'Friday'
> WHEN 1 THEN 'Saturday'
> WHEN 1 THEN 'Sunday'
> ELSE 'Unknown'
> END AS DescriptionOfDayOfWeek
> FROM T1;
>
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
>
> "Rico" <me@you.com> wrote in message news:41bVh.6137$j%5.1809@edtnps90...
>> Hello,
>>
>> For anyone that's a VB programmer out there, I'm looking for the
>> evivalent of a Choose statemetn in SQL Server. For those that aren't VB
>> guys, a choose statement will return a value, based on the numeric value
>> of the input. for instance;
>>
>> Choose(InputValue,[Return if InputValue=1],[Return if
>> InputValue=2],[Return if InputValue=3],[Return if InputValue=4],...etc)
>>
>> Example;
>> Choose(DayOfWeek,"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
>>
>> Any direction is greatly appreciated.
>>
>> Thanks!
>> Rick
>>
>>
>
>

"ML" <ML@discussions.microsoft.com> wrote in message
news:B324EFE7-207C-43B1-AC25-7E38BFBB12D2@microsoft.com...
> If only Copy/Paste were smarter... ;)
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

> For anyone that's a VB programmer out there, I'm looking for the evivalent
> of a Choose statemetn in SQL Server. For those that aren't VB guys, a
> choose statement will return a value, based on the numeric value of the
> input. for instance;

> Example;
> Choose(DayOfWeek,"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")

"Rico" <me@you.com> wrote in message news:jibVh.6142$j%5.3014@edtnps90...
> Thanks folks. I was afraid of that, was just hoping that there would be
> an easier way.

> CASE [DayOfWeek]
> WHEN BETWEEN 1 and 7

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%231$7xjTgHHA.1216@TK2MSFTNGP03.phx.gbl...
>> For anyone that's a VB programmer out there, I'm looking for the
>> evivalent of a Choose statemetn in SQL Server. For those that aren't VB
>> guys, a choose statement will return a value, based on the numeric value
>> of the input. for instance;
>
> You're looking for a case expression, e.g.
>
> DECLARE @InputValue INT;
> SET @InputValue = 2;
> SELECT CASE WHEN @InputValue = 1 THEN 'foo' WHEN @InputValue = 2 THEN
> 'bar' ELSE 'blat' END
>
>> Example;
>> Choose(DayOfWeek,"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
>
> Well, I suppose you mean to use this for a less trivial example. But just
> in case, you can do this with DATENAME and without getting the numeric
> constant for the day of the week, assuming you can trust your datefirst
> settings...
>
> SELECT DATENAME(DW, GETDATE());
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>