sql中的To_char日期仅适用于星期三

Question

我进行的这个查询返回特定日期中为特定职位雇佣的员工人数

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; 

基本上，只有当我在WHERE子句中键入"星期三"时，这个查询才有效，为什么？我如何修复它，使其与周日和周六一起工作

如果没有where子句，我会得到

Accountant  Friday      1
Accountant  Monday      1
Accountant  Tuesday     1
Accountant  Wednesday   2
Accounting Manager  Tuesday     1
Administration Assistant    Saturday    1

通过where子句，我得到

Accountant  Wednesday   2
Administration Vice President   Wednesday   2
Finance Manager Wednesday   1
Marketing Manager   Wednesday   1
Marketing Representative    Wednesday   1
Purchasing Clerk    Wednesday   2

我只是没有得到任何其他一天的结果

Answer 1

正是这两个字母：fm起了作用。

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>

正如您所看到的，len_day总是相同的：11，因为您使用了'Day'格式模型。您应该使用'fmDay'。

---

或者，更差的解决方案，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>