OraFAQ Forum: SQL & PL/SQL » Sysdate to_char DAY conversion not comparable

Home » SQL & PL/SQL » SQL & PL/SQL » Sysdate to_char DAY conversion not comparable (Oracle 10g and 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sysdate to_char DAY conversion not comparable [message #644972]

Mon, 23 November 2015 00:27

Rishab_le_noob
Messages: 12
Registered: November 2015
Location: Kolkata

Junior Member

Hi Friends,
This is a weird behavior/bug I found in Oracle 10g and 11g.

Issue: When we try to compare a date's weekday using the TO_CHAR(DAY) keyword, it fails. But it works fine with TO_CHAR('DY').
Anyone else faced this issues?
You guys think this is a bug?

Query:

select to_char(sysdate,'dd-mm-yyyy day') ,
case 
when to_char(sysdate,'day')='monday' then 'Takes the 1st one'
when to_char(sysdate,'dy')='mon' then 'Takes the 2nd one' 
end
from dual;

Result:
Takes the 2nd one

Where as it should have returned the 1st result : 'Takes the 1st one'

Thank You
Rishab

[Updated on: Mon, 23 November 2015 01:21]

Report message to a moderator

Re: Sysdate to_char DAY conversion not comparable [message #644978 is a reply to message #644972]

Mon, 23 November 2015 01:33

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Here's a clue:

SQL> select '"'||to_char(sysdate+level-1,'DAY')||'"' from dual connect by level<=7;
'"'||TO_CHA
-----------
"MONDAY   "
"TUESDAY  "
"WEDNESDAY"
"THURSDAY "
"FRIDAY   "
"SATURDAY "
"SUNDAY   "

7 rows selected.

Report message to a moderator

Re: Sysdate to_char DAY conversion not comparable [message #644979 is a reply to message #644978]

Mon, 23 November 2015 01:37

Rishab_le_noob
Messages: 12
Registered: November 2015
Location: Kolkata

Junior Member

Got it mate,

--Correct Query

select to_char(sysdate,'dd-mm-yyyy day') ,
case 
when trim(to_char(sysdate,'day'))='monday' then 'Takes the 1st one'
when to_char(sysdate,'dy')='mon' then 'Takes the 2nd one' 
end
from dual;

Result:
Takes the 1st one

Report message to a moderator

Re: Sysdate to_char DAY conversion not comparable [message #644981 is a reply to message #644979]

Mon, 23 November 2015 01:40

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Another way is to use the "FM" format model modifier:

SQL> select '"'||to_char(sysdate+level-1,'fmDAY')||'"' from dual connect by level<=7;
'"'||TO_CHA
-----------
"MONDAY"
"TUESDAY"
"WEDNESDAY"
"THURSDAY"
"FRIDAY"
"SATURDAY"
"SUNDAY"

7 rows selected.

Report message to a moderator

Previous Topic:	Convert Rows to Columns
Next Topic:	WITH clause working in 10g but not in 11g (XE)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 12 20:46:47 CDT 2026