| case expression [message #278357] |
Sat, 03 November 2007 05:14  |
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  |
|
|
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 #278373 is a reply to message #278357] |
Sat, 03 November 2007 08:56 |
|
|
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 #278381 is a reply to message #278373] |
Sat, 03 November 2007 10:31 |
 |
Michel Cadot
Messages: 65090
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
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
