CODEWITHSUNDEEP
sqloracle-database
Roostir
Roostir

Reputation: 1

How to return weekday name from date

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

Upvotes: 0

Views: 648

Answers (5)

i100
i100

Reputation: 4666

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;

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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.

Upvotes: 2

bl4ckb0l7
bl4ckb0l7

Reputation: 3999

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

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191245

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

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try below -

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

Upvotes: 0

Related Questions