Re: Anyone have a DAYS_BETWEEN function?

From: Mark Gumbs <mgumbs_at_hotmail.com>
Date: Wed, 3 Mar 1999 15:33:01 -0000
Message-ID: <36dd54f0.0@145.227.194.253>

The theory of finding the amount of days between ranges is pretty simple. Julian dates are defined as the amount of days since the 1st of January 4712 BC. Therefore, convert both your dates to julian and subtract one from the other. I'll supply a function i knocked together that does it.

To answer your other question about ADD_TO_DATE, try something along the lines of:

     select sysdate +100 from dual;

This will add 100 days to todays date. Just replace sysdate with the date variable of your choice in your function (not forgetting to to a to_char(to_date(...,...)) on your inputted date variable).

Other date tweaking functions you want to define can follow this lead.

Regards,

Mark

• ==========================================================
• Function Days_Between
  --
  
• By Mark Gumbs (3rd March 1999)
• E-Mail mgumbs_at_hotmail.com
  --
  
• Usage:
  --
  
• v_days := Days_Between('01-JAN-1996','01-JAN-1997');
  --
  
• Constraints:
  --
  
• Dates have to be passed as character strings in ''
• and in format DD-MON-YYYY
  --
  
• ==========================================================

Create or replace Function Days_Between (v_from_date in varchar2, v_to_date in varchar2) return number is

v_julian_date_from number;
v_julian_date_to number;

begin

• =====================================
• Convert both dates to julian dates
• =====================================

  select to_char(to_date(v_from_date,'DD-MON-YYYY'), 'j')   into v_julian_date_from
  from dual;

  select to_char(to_date(v_to_date,'DD-MON-YYYY'), 'j')   into v_julian_date_to
  from dual;

• =========================================
• Subtract one from the other and return
• =========================================

  return (v_julian_date_to - v_julian_date_from);

end;

cjdavies1999_at_my-dejanews.com wrote in message <7bi9m6$2m8$1_at_nnrp1.dejanews.com>...

>I am looking for a DAYS_BETWEEN function, similar to the MONTHS_BETWEEN
>function provided by Oracle.  I would also like to be able to have a more
>general form of the ADD_MONTHS function, something like ADD_TO_DATE(
>date_to_increment, increment ammount, increment_unit).
>
>I welcome any pointers to Web sites where I can find date functions for

Oracle

>would be greatly appreciated.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own

Received on Wed Mar 03 1999 - 09:33:01 CST