CODEWITHSUNDEEP
oracle-databasedatedayofweek
user1025901
user1025901

Reputation: 1909

How to get the week day name from a date?

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?

Upvotes: 84

Views: 410716

Answers (3)

cluelessgumshoe
cluelessgumshoe

Reputation: 73

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 )

Upvotes: -3

wolφi
wolφi

Reputation: 8361

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

Upvotes: 9

Zohaib
Zohaib

Reputation: 7116

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

Upvotes: 158

Related Questions