How to get the week day name from a date?

SQL> SELECT TO_CHAR(date '1982-03-09', 'DAY') day FROM dual;DAY---------TUESDAYSQL> SELECT TO_CHAR(date '1982-03-09', 'DY') day FROM dual;DAY---TUESQL> 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.)

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---------TUESDAYSQL> SELECT TO_CHAR(date '1982-03-09', 'DY', 'NLS_DATE_LANGUAGE = ENGLISH') day FROM dual;DAY---TUESQL> SELECT TO_CHAR(date '1982-03-09', 'Dy', 'NLS_DATE_LANGUAGE = ENGLISH') day FROM dual;DAY---Tue

To do this for oracle sql, the syntax would be:

,SUBSTR(col,INSTR(col,'-',1,2)+1) AS new_field

for this example, I look for the second '-' and take the substring to the end