Home » SQL & PL/SQL » SQL & PL/SQL » case expression
case expression [message #278357] Sat, 03 November 2007 05:14 Go to next message
dr46014
Messages: 48
Registered: February 2007
Member
select
(
case
when to_char(to_date('20071104','YYYYMMDD'),'DAY') in('SUNDAY','MONDAY') then to_char(to_date('20071104','YYYYMMDD')-2,'YYYYMMDD')
else to_char(to_date('20071104','YYYYMMDD')-1,'YYYYMMDD')
END
) START_DATE
from dual


this expression is always going to else condition even if its SUNDAY or MONDAY
please help me how can i resolve the prob
Re: case expression [message #278358 is a reply to message #278357] Sat, 03 November 2007 05:23 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> select
(
case
when trim(to_char(to_date
('20071104','YYYYMMDD'),'DAY')) in('SUNDAY','MONDAY') then to_char(to_date('20071104','YYYYMMDD')-2,'YYYYMMDD')
else to_char(to_date
('20071104','YYYYMMDD')-1,'YYYYMMDD')
END
) START_DATE
from dual  2    3    4    5    6    7    8  ;

START_DA
--------
20071102

SQL> select
(
case
when to_char(to_date
('20071104','YYYYMMDD'),'DAY') in('SUNDAY','MONDAY') then to_char(to_date('20071104','YYYYMMDD')-2,'YYYYMMDD')
else to_char(to_date
('20071104','YYYYMMDD')-1,'YYYYMMDD')
END
) START_DATE
from dual  2    3    4    5    6    7    8  ;

START_DA
--------
20071103






[edit:linesize]

[Updated on: Sat, 03 November 2007 05:24]

Report message to a moderator

Re: case expression [message #278370 is a reply to message #278357] Sat, 03 November 2007 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure your date language is in the english family?

Regards
Michel
Re: case expression [message #278371 is a reply to message #278358] Sat, 03 November 2007 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju,

I don't follow your exemples (which are quite scramble).
What did you try to show?

Regards
Michel
Re: case expression [message #278373 is a reply to message #278357] Sat, 03 November 2007 08:56 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Ok, Michel I may be wrong because I have not read sql documentation.

SQL> select length(to_char(to_date
('20071104','YYYYMMDD'),'DAY')  2  ) from dual;

LENGTH(TO_CHAR(TO_DATE('20071104','YYYYMMDD'),'DAY'))
-----------------------------------------------------
                                                    9

SQL> select length(trim(to_char(to_date('20071104','YYYYMMDD'),'DAY'))) from dual;

LENGTH(TRIM(TO_CHAR(TO_DATE('20071104','YYYYMMDD'),'DAY')))
-----------------------------------------------------------
                                                          6


So I used trim to match with SUNDAY which is 6 characters long.
Re: case expression [message #278378 is a reply to message #278373] Sat, 03 November 2007 10:22 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
You can also use the fill mode option...TO_CHAR(<date>, 'fmDAY') to eliminate the padded spaces
Re: case expression [message #278381 is a reply to message #278373] Sat, 03 November 2007 10:31 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, the expression was so long I didn't see the "trim".

SQL> select '"'||to_char(sysdate+level,'DAY')||'"' d1,
  2         '"'||to_char(sysdate+level,'FMDAY')||'"' d2
  3  from dual
  4  connect by level<=7
  5  /
D1          D2
----------- -----------
"SUNDAY   " "SUNDAY"
"MONDAY   " "MONDAY"
"TUESDAY  " "TUESDAY"
"WEDNESDAY" "WEDNESDAY"
"THURSDAY " "THURSDAY"
"FRIDAY   " "FRIDAY"
"SATURDAY " "SATURDAY"

7 rows selected.

Regards
Michel
Previous Topic: Inserting a new page
Next Topic: Sub Query / Lookup
Goto Forum:
  


Current Time: Fri Dec 09 19:48:33 CST 2016

Total time taken to generate the page: 0.12868 seconds