'To_char date in sql only works with wednesday [duplicate]

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

sqloracledatewhere-clause


Solution 1:[1]

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>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot

Related Questions