asked on

Show name of day in case of day number in T-SQL

Hi Experts,
I use the following command to create a dataset containing two columns. The first column shows the number of the day in the week and the second column shows the numbers of orders on a certain week day. The result is something like this:
1 50 (meaning 50 orders on sundays)
2 32 (32 orders on mondays)

This is nice but I would like a like to show the name of the days in case of their numbers.
So the result should be:
Sunday 50
Monday 32
...

My SQL command:

SELECT DATEPART (WEEKDAY, Bon_DtmBestelbon), COUNT(*)
FROM KL_Bestelbons
GROUP BY DATEPART(WEEKDAY, Bon_DtmBestelbon), Bon_Vrt_Id

How do I edit my command to get the wanted result???

HainKurt

try this:

select case col1
when 1 then 'Sunday'
when 2 then 'Monday'
..
end as WeekName, t.*
from (your original query here) t

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NoraWil

ASKER

Thank you very much. You, experts, are the true heroes of this world.

danrosenthal

How about this?

SELECT DATENAME(dw, Bon_DtmBestelbon-2), COUNT(*)
FROM               KL_Bestelbons
GROUP BY           Bon_DtmBestelbon, Bon_Vrt_Id

Open in new window

NoraWil

ASKER

The solution of DanRosenthal works too? It is even more simple. I prefer the first one, because my native language is dutch and the first solution makes it simple to localize the command.

danrosenthal

It would work for different languages using the SET LANGUAGE command:

SET LANGUAGE dutch
SELECT DATENAME(dw, 1)