Re: Math equation needed to solve SQL problem

<mrs_gleeson_at_my-deja.com> wrote in message news: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.

select ... from ...
where add_months(table_date,

    trunc(months_between(to_date('08/31/1999', 'mm/dd/yyyy'), table_date)/36)*36)  between to_date('08/01/1999', 'mm/dd/yyyy')

     and to_date('08/31/1999', 'mm/dd/yyyy')

Be ware that this statement overlooks the time part of a date. I don't know whether your table_date columns store time part or not, you must make some revision. Received on Thu Nov 04 1999 - 19:53:13 CST