How to get the week day name from a date?

小开

最佳答案

SQL> SELECT TO_CHAR(date '1982-03-09', 'DAY') day FROM dual;


DAY
---------
TUESDAY


SQL> SELECT TO_CHAR(date '1982-03-09', 'DY') day FROM dual;


DAY
---
TUE


SQL> SELECT TO_CHAR(date '1982-03-09', 'Dy') day FROM dual;


DAY
---
Tue

(Note that the queries use ANSI date literals, which follow the ISO-8601 date standard and avoid date format ambiguity.)

小开

To do this in Oracle sql, I tried like this and it worked for me

SELECT
START_DATE,
CASE WHEN START_DAY = 7 THEN 'SUNDAY'
WHEN START_DAY = 1 THEN 'MONDAY'
WHEN START_DAY = 2 THEN 'TUESDAY'
WHEN START_DAY = 3 THEN 'WEDNESDAY'
WHEN START_DAY = 4 THEN 'THURSDAY'
WHEN START_DAY = 5 THEN 'FRIDAY'
WHEN START_DAY = 6 THEN 'SATURDAY'
END DAY_NAME
FROM
(SELECT
TO_CHAR(T.START_DATE, 'DY') START_DAY,
TO_CHAR(T.START_DATE, 'MM/DD/YYYY') START_DATE
       

FROM
TABLE T )

小开

Nearly ten years late to the party, but I think it's neccessary to enhance @Zohaib 's answer as it's result depend on the language of the client. If you display the week day name to a user, it's absolutely fine, but if your code depends on it, I'd rather control the language like so:

SQL> SELECT TO_CHAR(date '1982-03-09', 'DAY', 'NLS_DATE_LANGUAGE = ENGLISH') day FROM dual;


DAY
---------
TUESDAY


SQL> SELECT TO_CHAR(date '1982-03-09', 'DY', 'NLS_DATE_LANGUAGE = ENGLISH') day FROM dual;


DAY
---
TUE


SQL> SELECT TO_CHAR(date '1982-03-09', 'Dy', 'NLS_DATE_LANGUAGE = ENGLISH') day FROM dual;


DAY
---
Tue