Home » SQL & PL/SQL » SQL & PL/SQL » show number of days between two date
show number of days between two date [message #582471] Thu, 18 April 2013 11:41 Go to next message
hassan08
Messages: 87
Registered: June 2011
Location: egypt
Member
i want to create function to show number of days between to days
such as number of Friday days between to dates
Re: show number of days between two date [message #582477 is a reply to message #582471] Thu, 18 April 2013 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, do it.

Regards
Michel
Re: show number of days between two date [message #582522 is a reply to message #582477] Fri, 19 April 2013 02:10 Go to previous messageGo to next message
pankajdurve
Messages: 18
Registered: April 2013
Location: MUMBAI
Junior Member
select round(sysdate- to_date('04-04-2013','dd-mm-yyyy')) from dual
Re: show number of days between two date [message #582534 is a reply to message #582522] Fri, 19 April 2013 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure this gives the "number of Friday days between to dates"?

Regards
Michel
Re: show number of days between two date [message #582545 is a reply to message #582471] Fri, 19 April 2013 06:19 Go to previous messageGo to next message
hassan08
Messages: 87
Registered: June 2011
Location: egypt
Member
no pankajdurve
i mean if i have two date such as 01-04-2009 , 01-08-2009
i want to creating function to calc number of Friday days between this days above
and i want this function dynamic

Re: show number of days between two date [message #582549 is a reply to message #582545] Fri, 19 April 2013 06:32 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
dynamic in what sense?
Re: show number of days between two date [message #582550 is a reply to message #582545] Fri, 19 April 2013 06:33 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
With a little help of a row generator, here's one option (that returns Fridays); have a look at datetime format models and fill in the gaps.
SQL> with days_gone_by as
  2    (select to_date('&&start_date', 'dd.mm.yyyy') + level - 1 datum
  3     from dual
  4     connect by level <=
  5       to_date('&&end_date', 'dd.mm.yyyy') - to_date('&&start_date', 'dd.mm.yyyy') + 1
  6    )
  7  select datum
  8  from days_gone_by
  9  where to_char(datum, <format mask here>) = <value here>;
Enter value for start_date: 01.04.2013
Enter value for end_date: 20.04.2013

DATUM
----------
05.04.2013
12.04.2013
19.04.2013

SQL>


What does "I want this function dynamic" mean?
Re: show number of days between two date [message #582564 is a reply to message #582550] Fri, 19 April 2013 07:50 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot wrote on Fri, 19 April 2013 07:33

What does "I want this function dynamic" mean?


My guess is that the dates are not fixed, but that is not really the definition of dynamic to me.
Re: show number of days between two date [message #582588 is a reply to message #582471] Fri, 19 April 2013 13:52 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hi,

please find the code may be it will give you some idea. Cool

May be you can find the next day and that next day you can convert it into
julian date and also convert greater date into julian minus it divide it and find the mod()
if the mode is greater than 0 than add one more day.

If this code will be helpful to you, you have to modify it according to your need,

May be you will have to modify the logic of this code

CREATE FUNCTION double_fun (p_first_date date, p_last_date date, which_day VARCHAR)
RETURN NUMBER

IS 

  h_many_time NUMBER(10);
  first_date_j NUMBER(10);
  second_date_j NUMBER(10);
  days_between NUMBER(10);
  no_of_days NUMBER(10);  
  a NUMBER(10);

begin 

----------------------------------------------- cONVERT IT IN julian DATE
  IF TO_CHAR(p_first_date,'DAY') = which_day THEN 
      first_date_j := TO_NUMBER(TO_CHAR(p_first_date,'j');

  ELSE 
      first_date_j := TO_NUMBER(TO_CHAR(NEXT_DAY(p_first_date,'TUESDAY'),'j'));
  END IF;

  
  second_date_j := TO_NUMBER(TO_CHAR(p_last_date,'j'));

-------------------------------------------------- //GET NUMBER OF DAYS

 
  days_between := second_date_j - first_date_j;

-------------------------------------- // IF days is <=1 then only one day will be otherwise divide it and find that if the mode if 
                                       // greater than 0 than add +1 
  IF days_between <= 7 THEN 
     no_of_days :=1;
  
  ELSE 
     no_of_days := days_between/7;

     IF  mod(days_between,7) > 0 THEN 
	no_of_days := no_of_days + 1;
     END IF;
  END IF;

------------------------------------------
  
  RETURN no_of_days;

EXCEPTION 
    ............// WRITE WHATEVER IS REQUIRED
    ........... 
     
END;
     
Re: show number of days between two date [message #582592 is a reply to message #582588] Fri, 19 April 2013 15:14 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
Seems a little convoluted to get the answer. A good effort, I am not knocking you, but two things.

1. It does not compile as you are missing a parenthesis in your first IF statement.
2. I tried it with sysdate-3 to sysdate-2 for number of Fridays and it came back with 1, which is incorrect.
Re: show number of days between two date [message #582799 is a reply to message #582592] Mon, 22 April 2013 12:42 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hello Joy_division,

Thanks for reply.

Please tell me what I should need to RETURN when,
1. It does not compile as you are missing a parenthesis in your first IF statement.
2. I tried it with sysdate-3 to sysdate-2 for number of Fridays and it came back with 1, which is incorrect.

in both situation.

Thanks.
Re: show number of days between two date [message #582802 is a reply to message #582799] Mon, 22 April 2013 12:51 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
ashwani0301 wrote on Mon, 22 April 2013 13:42
Hello Joy_division,

Thanks for reply.

Please tell me what I should need to RETURN when,
1. It does not compile as you are missing a parenthesis in your first IF statement.
2. I tried it with sysdate-3 to sysdate-2 for number of Fridays and it came back with 1, which is incorrect.



I don't quite understand your question. I told you two things that were wrong with the code. Just fix them.
Re: show number of days between two date [message #582829 is a reply to message #582545] Mon, 22 April 2013 23:00 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

this question was brilliantly answered in tom kyte site, please refer this link.http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1527805947756
Re: show number of days between two date [message #582835 is a reply to message #582829] Tue, 23 April 2013 00:30 Go to previous message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot already gave (one of) the brilliant solution above.

Regards
Michel
Previous Topic: how to know partticular user existed or not in datbase?
Next Topic: how to populate a distinct column in a table
Goto Forum:
  


Current Time: Sun Apr 20 04:04:59 CDT 2014

Total time taken to generate the page: 0.12615 seconds