Re: Math equation needed to solve SQL problem

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Fri, 5 Nov 1999 09:03:24 +0100
Message-ID: <7vu32s$5of$1@oceanite.cybercable.fr>

Assuming that from and to are in the same year, you can use:

v734> create table t (col date);
v734> insert into t values (to_date('08/10/1996','MM/DD/YYYY'));
v734> insert into t values (to_date('10/10/1996','MM/DD/YYYY'));
v734> insert into t values (to_date('02/01/1994','MM/DD/YYYY'));
v734> commit;
v734> def from='08/01/1999'
v734> def to='08/31/1999'
v734> select to_char(col, 'MM/DD/YYYY') col from t
  2  where /* verify year */
  3        mod(to_number(substr('&from',7,4))-to_number(to_char(col, 'YYYY')),3)

= 0
  4 and /* verify month and day */

  5        to_char(col, 'MM/DD') between substr('&from',1,5)
  6                                  and substr('&to',1,5)

  7 /

COL

---

08/10/1996

1 row selected.

--
Have a nice day
Michel

<mrs_gleeson_at_my-deja.com> a écrit dans le message : 7vsd2b$vrn$1_at_nnrp1.deja.com...
> Hi,
> I have a user entered date range (from, to).
> Lets say (08/01/1999- 08/31/1999)
> In the table I have record w/ unkown differnet dates. I need to pick
> the dates from the table that if I keep adding 3 years to them, they
> end up in the above date range.
>
> Example:
> the table_date (08/10/1993) if I keep adding 3 years to it, it will end
> in the range
> (08/10/1993 + 3 yrs = 08/10/1996). Add another 3 yrs
> (08/10/1996 + 3 yrs = 08/10/1996). Add another 3 yrs = (08/10/1996 + 3
> yrs = 08/10/1999) which is in the range (08/01/1999- 08/31/1999).
>
> 2ND Example:
> the table_date (02/01/1994 + 3 yrs = 02/01/1997). Add another 3 yrs
> (02/01/1997 + 3 yrs = 02/01/2000) which is not in the range
> (08/01/1999- 08/31/1999).
>
> Please help! I need a math wizard to give me ONE select statement that
> contains a mathematical equation. Maybe there is a way in Oracle's
> function that can tell me if the table_date is between the date range
> or not????
> Thanks in advance.
> --MS.Gleeson
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Nov 05 1999 - 02:03:24 CST