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

Posted on 2011-05-05
Last Modified: 2012-08-13
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:

FROM                       KL_Bestelbons
GROUP BY                     DATEPART(WEEKDAY, Bon_DtmBestelbon), Bon_Vrt_Id

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

Question by:NoraWil
    LVL 51

    Expert Comment

    try this:

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

    Accepted Solution

    or this
    	DATEPART(WEEKDAY, Bon_DtmBestelbon) WeekNum, 
    	case DATEPART(WEEKDAY, Bon_DtmBestelbon)
    	when 1 then 'Sunday'
    	when 2 then 'Monday'
    	when 7 then 'Saturday'
    	end as WeekName,
    	COUNT(*) TotalSale
      FROM KL_Bestelbons
     GROUP BY DATEPART(WEEKDAY, Bon_DtmBestelbon), Bon_Vrt_Id

    Open in new window


    Author Closing Comment

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

    Expert Comment

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

    Open in new window


    Author Comment

    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.
    LVL 15

    Expert Comment

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

    SET LANGUAGE dutch

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now