[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-05
6
Medium Priority
?
292 Views
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:

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???

0
Comment
Question by:NoraWil
  • 2
  • 2
  • 2
6 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35700874
try this:

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

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 35700905
or this
SELECT 
	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

0
 

Author Closing Comment

by:NoraWil
ID: 35701010
Thank you very much. You, experts, are the true heroes of this world.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 15

Expert Comment

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

Open in new window

0
 

Author Comment

by:NoraWil
ID: 35701115
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.
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 35702324
It would work for different languages using the SET LANGUAGE command:

SET LANGUAGE dutch
SELECT DATENAME(dw, 1)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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