SQL: Convert Date to WeekDay doesn't work

Question

my Problem is to Display the weekday instead of the date. At the Moment this works:

SELECT
Sum(PostQuantity) AS Amount, 
to_char(PostingDate, 'dd-mm-yyyy') AS Day
FROM 
table1
GROUP BY to_char(PostingDate, 'dd-mm-yyyy') 
ORDER BY to_char(PostingDate, 'dd-mm-yyyy')

but this will show the date. Adding functions like:

SELECT
Sum(PostQuantity) AS Amount, 
DatePart(Weekday,PostingDate) AS Day

or

SELECT
Sum(PostQuantity) AS Amount, 
DateName(dw,PostingDate) AS Day

won't work. I think my problem is the use of the PostingDate in the SELECT Operator, but my try for fixing it didn't work as well.

sql server doesn't have a function `to_char()`, do you mean Oracle? — HoneyBadger, Aug 28 '17 at 13:53
Also, 'won't work' is not a problem description. *Why* didn't those queries work? Did you add the `datepart` function in the `group by` as well? — HoneyBadger, Aug 28 '17 at 13:55
i have to use MS Excel for my work, and thus the errormessage gives no hint about what's wrong — Marklord96, Aug 28 '17 at 14:01
Possible duplicate of [How to get the week day name from a date?](https://stackoverflow.com/questions/8004645/how-to-get-the-week-day-name-from-a-date) — Dan Bracuk, Aug 28 '17 at 14:06
What is the requirement? Do you want to group by individual dates, but in the output you want to display the day of the week? Or do you want to aggregate all values from Mondays (say), no matter what the date, into a single group? The Answer you accepted as Correct does not do the same computations as your original query - it didn't change only the SELECT clause, it also changed what the grouping is based on. — , Aug 28 '17 at 14:23
In my case it doesn't matter, because i have a `where` in which i filter for all records of this week, so it won't make any difference — Marklord96, Aug 28 '17 at 14:32

score 1 · Accepted Answer · answered Aug 28 '17 at 14:01

1

I don't see the problem in your code, but sometimes trying an alternative works. Try this:

SELECT SUM(t.postQuantity) as amount,
       TO_CHAR(t.PostingDate,'DAY') as dayOfTheWeek
FROM YourTable t
GROUP BY TO_CHAR(t.PostingDate,'DAY')

Dy will give you first 3 letters.

answered Aug 28 '17 at 14:01

sagi

40,026
6
59
84

This is not equivalent to the original query though. The original query grouped by date. This version will aggregate by putting all Mondays in the same group (regardless of date). Perhaps this is what the OP wanted, but it's not clear. – Aug 28 '17 at 14:21
I think that's what he intended because as you can see he tried to convert this row to something else (the `SUM(postQuantity)` remaind. @mathguy – sagi Aug 28 '17 at 14:24
But so did the GROUP BY clause - he only tried to change the SELECT. In any case, this type of doubt is always worth a question. I just asked the OP in a Comment under the original question. – Aug 28 '17 at 14:25
Indeed it does, didn't even think of it, just tried to supply an alternative. Let's see what he answers. – sagi Aug 28 '17 at 14:26

score 0 · Answer 2 · answered Aug 28 '17 at 14:01

0

You can use the DATENAME function if it's on sql

SELECT DATENAME(WEEKDAY,[Date])
FROM Table1

In Oracle

select to_char(to_date('03/09/1982','dd/mm/yyyy'), 'DY')

answered Aug 28 '17 at 14:01

user3317519

80
1
11

what it will return week day name like Saturday, sunday ..... or week index like 0, 1, .. – ajit Jan 16 '21 at 09:48

SQL: Convert Date to WeekDay doesn't work

2 Answers2