Finding specific date [message #328487] |
Fri, 20 June 2008 05:45 |
subhadip.chanda
Messages: 64 Registered: May 2007
|
Member |
|
|
Hi,
I am facing problem when I want to extract specific date depending upon the input.Suppose i have input date is
'4-mar-2008'.As it falls between 1 and 10 march I need my output as '1-mar-2008' and '10-march-2008'.If it is '15-mar-2008',then output dates will be '11-mar-2008' and '20-mar-2008'.and if the input date falls between 21 and 31 st march then I need 21-march and 31st march '2008 as my output.
please help.
|
|
|
|
Re: Finding specific date [message #328503 is a reply to message #328487] |
Fri, 20 June 2008 06:09 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Crude but effective approach:create table date_Test (col_1 date);
insert into date_test values (to_Date('4-Mar-2008','dd-mon-yyyy'));
insert into date_test values (to_Date('11-Mar-2008','dd-mon-yyyy'));
insert into date_test values (to_Date('15-Mar-2008','dd-mon-yyyy'));
insert into date_test values (to_Date('20-Mar-2008','dd-mon-yyyy'));
insert into date_test values (to_Date('25-Mar-2008','dd-mon-yyyy'));
insert into date_test values (to_Date('31-Mar-2008','dd-mon-yyyy'));
select col_1
,to_date(start_day||rest_of_date,'dd Mon yyyy') start_date
,to_date(end_day ||rest_of_date,'dd Mon yyyy') end_date
from (select col_1,
case when to_char(col_1,'dd') between 1 and 10 then 1
when to_char(col_1,'dd') between 11 and 20 then 11
else 21 end start_day,
case when to_char(col_1,'dd') between 1 and 10 then 10
when to_char(col_1,'dd') between 11 and 20 then 20
else to_number(to_char(last_day(col_1),'dd')) end end_day,
to_char(col_1,' Mon yyyy') rest_of_date
from date_Test);
COL_1 START_DAT END_DATE
--------- --------- ---------
04-MAR-08 01-MAR-08 10-MAR-08
11-MAR-08 11-MAR-08 20-MAR-08
15-MAR-08 11-MAR-08 20-MAR-08
20-MAR-08 11-MAR-08 20-MAR-08
25-MAR-08 21-MAR-08 31-MAR-08
31-MAR-08 21-MAR-08 31-MAR-08
|
|
|