Re: ??? Number of days betwwen two dates?

From: Cecil <cecil_at_total.net>
Date: 1997/07/30
Message-ID: <33df9b33.0@news.total.net>

This is a multi-part message in MIME format.

------=_NextPart_000_0003_01BC9D00.16340500 Content-Type: multipart/alternative;

boundary="----=_NextPart_001_0004_01BC9D00.163D2CC0"

------=_NextPart_001_0004_01BC9D00.163D2CC0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Huang,

If you buy Steven Feuersteins Book on PL/SQL programming which

you must, you will get all these problems sorted out with a disk of = scripts.

Cecil

Huang, Joseph wrote in article <5rnq02$d28_at_bolivia.earthlink.net>...

I need a function, which I can use in other stored procedures, to = return number of days of two given dates. The only similar function I = can found in Oracle manual is MONTHS_BETWEEN(). However, the problem is = MONTHs_BETWEEN() return number based on 31-day month which is incorrect = normally. For example, instead of 29 days, the function returns = 1.0322581 (32 days) from 02/01/97 to 03/02/97.=20

Before writing our own function to get correct number of days, I'd like = to know whether this kind of function exists somewhere so I don't need = to redo it. Any suggestions? =20

Thanks in advance. =20

Joseph Huang =20

--=20
My email address is intentionally invalid to foil spammers. Delete the = ".---" to get my real address.=20

------=_NextPart_001_0004_01BC9D00.163D2CC0 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>

<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type><!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 = HTML//EN"><HTML><HEAD>
<META content=3D'"MSHTML 4.71.1008.3"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
 Huang,
If you buy Steven =
Feuersteins=20
Book on PL/SQL programming which
you must, you will get =
all these=20
problems sorted out with a disk of scripts.
Cecil
Huang, Joseph wrote in article <<A=20
href=3D"mailto:5rnq02$d28_at_bolivia.earthlink.net">5rnq02$d28_at_bolivia.earth= link.net</A>>...
 I need a =
function,=20
which I can use in other stored procedures, to return number of days of = two=20
given dates. The only similar function I can found in Oracle manual is=20 MONTHS_BETWEEN(). However, the problem is MONTHs_BETWEEN() return number = based=20
on 31-day month which is incorrect normally. For example, instead of 29 = days,=20
the function returns 1.0322581 (32 days) from 02/01/97 to = 03/02/97. 
Before writing=20
our own function to get correct number of days, I'd like to know whether = this=20
kind of function exists somewhere so I don't need to redo it. Any=20 suggestions? =20
Thanks in=20
advance. =20
Joseph=20
Huang =20
 -- My =
email address=20
is intentionally invalid to foil spammers.  Delete the = ".---" to=20
get my real address. </BODY></HTML>

------=_NextPart_001_0004_01BC9D00.163D2CC0--

------=_NextPart_000_0003_01BC9D00.16340500 Content-Type: application/octet-stream;

name="Daternge.sf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;

filename="Daternge.sf"

FUNCTION date_range

	(start_date_in IN DATE, 
	 end_date_in IN DATE,
	 check_time_in IN VARCHAR2 := 'NOTIME')

RETURN VARCHAR2
/*

|| date_range returns a string containing a date range
|| in the format 'BETWEEN x AND y'
||
|| Parameters:
||	start_date_in - The start date of the range. If NULL
||		then use the min_start_date. If that is NULL, range 
||		has form '<= end_date'.
||
||	end_date_in - The end date of the range. If NULL
||		then use the max_end_date. If that is NULL, range has 
||		form '>= start_date'.
||
||	check_time_in - If 'TIME' then use the time component
||		of the dates as part of the comparison.
||		If 'NOTIME' then strip off the time.

*/
IS

	/* String versions of parameters to place in return value */
	   start_date_int VARCHAR2(30);
	   end_date_int VARCHAR2(30);

	/* Date mask for date<->character conversions. */
	   mask_int VARCHAR2(15) := 'MMDDYYYY';

	/* Version of date mask which fits right into date range string */
	   mask_string VARCHAR2(30) := NULL;

	/* The return value for the function. */

return_value VARCHAR2(1000) := NULL;

BEGIN

	/*
	|| Finalize the date mask. If user wants to use time, add that to
	|| the mask. Then set the string version by embedding the mask
	|| in single quotes and with a trailing paranthesis.
	*/
	IF UPPER (check_time_in) = 'TIME'
	   THEN 
		mask_int := mask_int || ' HHMISS';
	END IF;
	/*
	|| Convert mask. Example: 
	|| 	If mask is:				MMDDYYYY HHMISS
	||   	   then mask string is: ', 'MMDDYYYY HHMISS')
	*/
	mask_string := ''', ''' || mask_int || ''')';

	/* Now convert the dates to character strings using format mask */
           start_date_int := TO_CHAR (start_date_in, mask_int);
	   end_date_int := TO_CHAR (end_date_in, mask_int);

	/* If both start and end are NULL, then return NULL. */
	   IF start_date_int IS NULL AND end_date_int IS NULL 
	      THEN
		return_value := NULL;

        /* If no start point then return "<=" format. */
	   ELSIF start_date_int IS NULL
	      THEN
		return_value := '<= TO_DATE (''' || end_date_int || mask_string;

	/* If no end point then return ">=" format. */
	ELSIF end_date_int IS NULL
	THEN
	     return_value := '>= TO_DATE (''' || start_date_int || mask_string;

	/* Have start and end. A true range, so just put it together. */
	ELSE
		return_value :=
		  'BETWEEN TO_DATE (''' || start_date_int || mask_string || 
		     ' AND TO_DATE (''' || end_date_int || mask_string; 
	END IF;

	RETURN return_value;

END; ------=_NextPart_000_0003_01BC9D00.16340500-- Received on Wed Jul 30 1997 - 00:00:00 CDT