How to return weekday name from date

Question

I am trying to add a column next to my existing date column which gives the corresponding name of the day of the week.

SELECT DISTINCT
a.transaction_date,
to_char(date 'a.transaction_date','DAY') as Day
FROM transactions_table a

Trying for: Mon, Tue, Wed, etc.. Sadly, the database is returning ORA-01841 error instead. Normally, my date column returns a date like this: dd-mmm-yy

Take a look at this: https://stackoverflow.com/questions/8004645/how-to-get-the-week-day-name-from-a-date — ravioli, Sep 10 '19 at 13:13
What data type is `a.transaction_date`? And why have you put that inside single quotes, tryign to make it part of a date *literal* when it is a column? — Alex Poole, Sep 10 '19 at 13:14
Also you've used the DAY format model and referred twice to the day *name*; but then showed the day *abbreviations* (Mon rather than Monday). Which do you actually want? And in all-uppercase or mixed-case? — Alex Poole, Sep 10 '19 at 13:47
Yep, I shouldn't have had single quotes as the field is a DATE field. Full uppercase name is fine. — Roostir, Sep 10 '19 at 14:05

Barbaros Özhan · Answer 1 · 2019-09-10T13:26:18.770

2

You can try Dy pattern :

select to_char(transaction_date,'Dy') 
  from transactions_table

If you need a different language consider a third argument :

select to_char(transaction_date,'Dy','nls_date_language=turkish')
  from transactions_table

P.S. I considered English language as default in the first query, if it's not default language for your database, then consider the second query by replacing turkish with english.

edited Sep 10 '19 at 13:26

answered Sep 10 '19 at 13:17

Barbaros Özhan

59,113
10
31
55

you're welcome @Roostir , if you find the answer helpful, please accept it, by this way, the question is considered as being solved. – Barbaros Özhan Sep 10 '19 at 15:11

score 0 · Answer 2 · answered Sep 10 '19 at 13:13

0

Try below -

SELECT DISTINCT
a.transaction_date,
to_char(date a.transaction_date,'DAY') as Day
FROM transactions_table a

answered Sep 10 '19 at 13:13

Fahmi

37,315
5
22
31

3

There is a spurious `DATE` keyword inside the `to_char()` function and the `DAY` format model will return the full name of the day rather than the three-letter abbreviated version. – MT0 Sep 10 '19 at 13:21

i100 · Answer 3 · 2019-09-10T13:33:21.327

0

I suppose that transaction_date is a date datatype...

SELECT DISTINCT
a.transaction_date,
to_char(a.transaction_date,'DAY') as Day
FROM transactions_table a

if it is varchar2 then you could just convert it to date first, using your date format

SELECT DISTINCT
a.transaction_date,
to_char(to_date(a.transaction_date, 'dd/mm/yyyy'),'DAY') as Day
FROM transactions_table a

for instance:

select to_char(to_date('08/09/2019', 'dd/mm/yyyy'),'DAY') as Day from dual;

edited Sep 10 '19 at 13:33

answered Sep 10 '19 at 13:18

i100

4,529
1
22
20

2

`'a.transaction_date'` is a string literal. Without the single quotes is a table alias and column name. Also, the `DAY` format model will return the full name of the day not the abbreviated version of the name. – MT0 Sep 10 '19 at 13:26
Yep, it's a DATE type. Your first solution works. Thanks! – Roostir Sep 10 '19 at 14:07

score 0 · Answer 4 · answered Sep 10 '19 at 13:19

date 'a.transaction_date' is trying to mix a date literal with a variable column value, but then also putting the column name inside single quotes, You end up essesntially doing:

to_date('a.transaction_date', 'YYYY-MM-DD')

which also gets ORA-01841, for perhaps clearer reasons.

If transaction_date is already a date then just refer to that:

SELECT DISTINCT
a.transaction_date,
to_char(a.transaction_date,'DAY') as Day
FROM transactions_table a

If it's a string - which it should not be! - then convert that value to a date properly:

SELECT DISTINCT
a.transaction_date,
to_char(to_date(a.transaction_date, 'YYYY-MM-DD'),'DAY') as Day
FROM transactions_table a

... or using whatever format mask is appropriate.

Also be aware that DAY (and the abbreviated DY, or Dy, which might be what you actually want) output depends on your NLS date language, but you can override that if you want it to always be in English, for instance:

SELECT DISTINCT
a.transaction_date,
to_char(a.transaction_date,'DY','NLS_DATE_LANGUAGE=ENGLISH') as Day
FROM transactions_table a

Quick demo using a CTE:

-- CTE for dummy date
with transactions_table(transaction_date) as (
  select date '2019-01-01' from dual
  union all
  select date '2019-09-01' from dual
)
-- actual query
SELECT DISTINCT
a.transaction_date,
to_char(a.transaction_date,'Dy','NLS_DATE_LANGUAGE=ENGLISH') as Day
FROM transactions_table a
/

TRANSACTIO DAY         
---------- ------------
2019-01-01 Tue         
2019-09-01 Sun

score 0 · Answer 5 · answered Sep 10 '19 at 13:23

0

Check out Format Models

SELECT DISTINCT
a.transaction_date,
to_char(a.transaction_date, 'DY') as Day_Abbr, -- This will print out an abbriviation like "MO" for Monday
to_char(a.transaction_date, 'DAY') as Day, -- This will print out a whole day, like "MONDAY"
substr(to_char(a.transaction_date, 'DAY'),1,3) as Day_My_Abbr -- This will print out your wanted abbriviation, like "MON"
FROM transactions_table a

answered Sep 10 '19 at 13:23

bl4ckb0l7

3,839
4
28
33

1

'MONTAG' is abbreviated to 'MO'; if the date language is set to English then you get 'MONDAY' and 'MON' - no need to substr. (The OP has shown the English abbreviation, so I'm assuming that's the language they're using by default...) – Alex Poole Sep 10 '19 at 13:29

How to return weekday name from date

5 Answers5