This query that I made returns the number of employees hired for an specific position in a specific day
SELECT job_title ,To_char(hire_date, 'Day') AS HIRE_DAY,
Count(*) AS cc
FROM employees
WHERE To_char(hire_date,'Day')='Sunday' OR To_char(hire_date,'Day')='Wednesday'
GROUP BY job_title,To_char(hire_date, 'Day')
ORDER BY job_title ASC;
Basically this query only works when I type 'Wednesday' in the WHERE clause, Why is this? How can I fix it so it works with Sunday and Saturday
Without the where clause I get
Accountant Friday 1
Accountant Monday 1
Accountant Tuesday 1
Accountant Wednesday 2
Accounting Manager Tuesday 1
Administration Assistant Saturday 1
and with the where clause I get
Accountant Wednesday 2
Administration Vice President Wednesday 2
Finance Manager Wednesday 1
Marketing Manager Wednesday 1
Marketing Representative Wednesday 1
Purchasing Clerk Wednesday 2
I just don't get any results with any other day
It's those two letters: fm that make the difference.
SQL> with temp (datum) as
2 (select trunc(sysdate, 'mm') + level - 1
3 from dual
4 connect by level <= 7
5 )
6 select to_char(datum, 'dd.mm.yyyy, day') datum,
7 to_char(datum, 'Day') day,
8 length(to_char(datum, 'Day')) len_day,
9 --
10 to_char(datum, 'fmDay') day2,
11 length(to_char(datum, 'fmDay')) len_day2
12 from temp;
DATUM DAY LEN_DAY DAY2 LEN_DAY2
------------------------- ---------- ---------- ---------- ----------
01.04.2022, friday Friday 9 Friday 6
02.04.2022, saturday Saturday 9 Saturday 8
03.04.2022, sunday Sunday 9 Sunday 6
04.04.2022, monday Monday 9 Monday 6
05.04.2022, tuesday Tuesday 9 Tuesday 7
06.04.2022, wednesday Wednesday 9 Wednesday 9
07.04.2022, thursday Thursday 9 Thursday 8
7 rows selected.
SQL>
As you can see, len_day is always the same: 11, because you used the 'Day' format model. You should have used 'fmDay' instead.
Or, worse solution, trim:
SQL> select to_char(sysdate, 'Day') today,
2 length(to_char(sysdate, 'Day')) len_today,
3 --
4 trim(to_char(sysdate, 'Day')) today2,
5 length(trim(to_char(sysdate, 'Day'))) len_today2
6 from dual;
TODAY LEN_TODAY TODAY2 LEN_TODAY2
-------------------- ---------- -------------------- ----------
Monday 9 Monday 6
SQL>