Home » SQL & PL/SQL » SQL & PL/SQL » Finding specific date (Database 10g Enterprise Edition Release 10.1.0.2.0)
Finding specific date [message #328487] Fri, 20 June 2008 05:45 Go to next message
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 #328495 is a reply to message #328487] Fri, 20 June 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will help you:
SQL> with data as (select 4 val from dual union select 15 from dual union all select 25 from dual)
  2  select val, trunc(val,-1)+1 from data;
       VAL TRUNC(VAL,-1)+1
---------- ---------------
         4               1
        15              11
        25              21

3 rows selected.

Regards
Michel
Re: Finding specific date [message #328503 is a reply to message #328487] Fri, 20 June 2008 06:09 Go to previous message
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
Previous Topic: User Expire
Next Topic: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting
Goto Forum:
  


Current Time: Sun Dec 11 08:27:47 CST 2016

Total time taken to generate the page: 0.06341 seconds