CODEWITHSUNDEEP
sqloracle-databaseoracle10g
03Usr
03Usr

Reputation: 3435

Oracle get day name returns wrong name

I am using this to get the name of a given date, so the following gives me MONDAY:

Select to_char(to_date('01/04/2013','dd/mm/yyyy'), 'DAY') from dual

However if I apply the same thing to a table and extract dates with their names I am getting a wrong day name, in the case of '01/April/2013' it gives me SATURDAY.

    Select to_char(to_date(myDateColumn,'dd/mm/yyyy'), 'DAY'), myDateColumn 
From myTable WHERE myDateColumn = to_date('01/04/2013','dd/mm/yyyy')

This is how I would have done on MS SQL, but I need to do this for Oracle 10g database, is this the right way?

Thanks

Upvotes: 0

Views: 5743

Answers (3)

Alex Poole
Alex Poole

Reputation: 191580

This is partially following up other answers, but explaining (hopefully) what's happening.

When you do this, assuming myDateColumn is a DATE:

to_char(to_date(myDateColumn,'dd/mm/yyyy'), 'DAY')

you have an implicit conversion; it's really doing this:

to_char(to_date(to_char(myDateColumn),'dd/mm/yyyy'), 'DAY')

which is:

to_char(to_date(to_char(myDateColumn, <NLS_DATE_FORMAT>),'dd/mm/yyyy'), 'DAY')

Since you get SATURDAY, it looks like maybe your NLS_DATE_FORMAT is 'DD/MM/YY', or at least that's the only way I've found to duplicate this so far:

alter session set nls_date_format = 'DD/MM/YYYY';

select date '2013-04-01' date1,
    to_char(date '2013-04-01', 'Day') day1,
    to_date(to_char(date '2013-04-01', 'dd/mm/yy'), 'dd/mm/yyyy') date2,
    to_char(to_date(to_char(date '2013-04-01', 'dd/mm/yy'), 'dd/mm/yyyy'),
        'Day') day2
from dual;

DATE1      DAY1      DATE2      DAY2
---------- --------- ---------- ---------
01/04/2013 Monday    01/04/0013 Saturday

So even though your myDateColumn really holds 2013-04-01, your implcit conversion means it's being treated as 0013-04-01, which is (apparently) a Saturday.

You could specify the format and make it an explicit to_char() inside the to_date(), but hopefully it's clear that's pointless, and it's much simpler to do as vc74 suggested and just drop the extra - erronerous - to_date() call:

select to_char(myDateColumn, 'DAY'), ...

It's maybe worth pointing out that the text produced by DAY is also dependent on your NLS settings; see the documentation here and here, for example. If you wanted to make sure it was always MONDAY, in English, you could force that as part of the conversion, though I don't think it looks like it'll be a consideration here:

select to_char(myDateColumn, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH'), ...

Upvotes: 6

vc 74
vc 74

Reputation: 38179

Since your date column is a date:

Select to_char(myDateColumn, 'DAY'), myDateColumn 
From myTable 
WHERE myDateColumn = to_date('01/04/2013','dd/mm/yyyy')

should suffice

(this should be a comment, not an answer but it wouldn't be readable)

Upvotes: 4

David Aldridge
David Aldridge

Reputation: 52396

You seem to be confused about the data type for myDateColumn.

You imply that it is a date with:

WHERE myDateColumn = to_date('01/04/2013','dd/mm/yyyy')

... but also that it is a string with:

to_char(to_date(myDateColumn,'dd/mm/yyyy'), 'DAY')

See what it really is, and do not convert dates to dates or compare strings to dates.

Upvotes: 1

Related Questions