MS SQL Server - Day Of The Week Aggregate

Author

Day Of The Week Aggregate

Nate.Strack@gmail.com

2006-10-24, 6:36 pm

I'm looking for a way to turn a list of dates into a string of
abbreviations

10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be great

Arnie Rowland

2006-10-24, 6:36 pm

Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

<Nate.Strack@gmail.com> wrote in message
news:1160461194.481594.286540@i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
>
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>

Arnie Rowland

2006-10-24, 6:36 pm

Arnie Rowland

2006-10-24, 6:36 pm

Darn spellcheck.

Check into the use of datename().

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"Arnie Rowland" <arnie@1568.com> wrote in message
news:uJWcpaD7GHA.4500@TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> <Nate.Strack@gmail.com> wrote in message
> news:1160461194.481594.286540@i42g2000cwa.googlegroups.com...
>
>

Uri Dimant

2006-10-24, 6:36 pm

Hi

DECLARE @dt DATETIME
SET @dt='20061010'

SELECT LEFT(DATENAME(weekda
y,@dt),1)

<Nate.Strack@gmail.com> wrote in message
news:1160461194.481594.286540@i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
>
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>

Nate.Strack@gmail.com

2006-10-24, 6:36 pm

This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007

using syntax like
select Id, DateFunction(program
date)
from table1
group by id

and would return

1, '-M-W--S'

Uri Dimant wrote:[color=darkred
]
> Hi
>
> DECLARE @dt DATETIME
> SET @dt='20061010'
>
> SELECT LEFT(DATENAME(weekda
y,@dt),1)
>
>
>
>
> <Nate.Strack@gmail.com> wrote in message
> news:1160461194.481594.286540@i42g2000cwa.googlegroups.com...

Hugo Kornelis

2006-10-24, 6:37 pm

On 10 Oct 2006 18:08:31 -0700, Nate.Strack@gmail.com wrote:

>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>
>using syntax like
>select Id, DateFunction(program
date)
>from table1
>group by id
>
>and would return
>
>1, '-M-W--S'

Hi Nate,

Try:

SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;

--
Hugo Kornelis, SQL Server MVP

Sponsored Links