How to get the week day name from a date?

Question

Given 03/09/1982 how can we say it is which week day. In this case it will be Tue.

Is it possible to get in a single query?

score 150 · Accepted Answer · edited Jun 01 '18 at 16:46

150

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.)

edited Jun 01 '18 at 16:46

Community

1
1

answered Nov 04 '11 at 03:44

Zohaib

7,026
3
26
35

5

Be aware that the 'dd/mm/yyyy' in the code examples above is non-typical, if what you are going for is US date formatting. In US, typical date format would be 'mm/dd/yyyy'. It's unclear whether the original date example was in March or in September. – DWright Dec 16 '14 at 17:09
3

Non-typical? @DWright you must be from the usa and never ever have worked on an international proyect or heard about international standars (ISO) – pablete Apr 09 '15 at 14:51
16

@pablete, I actually grew up in Europe and am aware of international variations in date formatting and also am aware of ISO standards. The reason I made that comment is that the OP was clearly using a US date format, since March 9th, 1982 was a Tuesday, but September 3rd 1982 was a Friday. OP says it was a Tuesday, so we know US date formatting is being used. But Zohaib is using a 'dd/mm/yyyy' format string, thus causing a potential problem if OP uses Zohaib's format string, since it is not a US formatting. So I wanted to just alert people, and particularly the OP, to that. – DWright Apr 09 '15 at 17:16
1

Call me crazy but I would have added and used `select to_char(date '1982-03-09', 'Day') day from dual;`. No idea why people are so keen on capitals, since we have syntax highlighters now and capital letters take up more space when not using fixed width fonts. – Superdooperhero May 13 '21 at 07:44
Note that this appears to add trailing spaces to day names to make them as long as the longest possible day name ("WEDNESDAY"). So instead of "SUNDAY" you'll get "SUNDAY ". Prepending "fm" ("fill mode") appears to address this: `TO_CHAR(date '1982-03-09', 'fmDAY')`. – M. Justin Mar 31 '23 at 16:21

score 6 · Answer 2 · answered Apr 05 '21 at 09:17

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

score -3 · Answer 3 · edited Mar 17 '22 at 09:17

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 )

How to get the week day name from a date?

3 Answers3

Linked

Related