Re: months_between.

[Lori Donnelly <lorid@ONTKO.COM>]

Hello,

I just found out about this (Thanks, Ray), you can use the same Oracle
months_between in SQR.

begin-select
months_between(trunc(date_worked,'MM'),
                 to_date($start_date,'YYYYMMDD')) &months_between
from....
where...
and td.date_worked >= to_date($start_date,'YYYYMMDD')
and td.date_worked <= to_date($end_date,'YYYYMMDD')...
end-select

For example if your begin date is 19980701 and date_worked is 19990915
(which is 'trunc'ed to '09') &months_between would be '14' if
date_worked is 19980715 &months_between would be '0'.  You can also do
this with 'Q' you would just divide the whole thing by 3.

This basically tells you where date_worked falls in your date range.
You might need a groups by depending on what you are doing.

Hope this helps.
Lori
the dragon wrote:

> What database are you using????  If you are using Oracle, you can use the
> between fcn...
>
> psuedo-code:
>
> where ever the prompt happens; where #No_of_Month_Input is input/defined
>
> set $start_date = sysdate - #No_of_Month_Input
>
> and in your select
>
> ...
> EFFDT,
> ...
> from
> ....
> where
> ...
> and   effdt between $start_date and sysdate (or $end_date)
>
> If you aren't using Oracle, and your psuedo-database doesn't allow the
> between fcn, get a real database (Oracle)   :-) heheheheh
>
> HTH
> clark 'the dragon' willis
> dragon enterprises, consulting services
>
> ----Original Message Follows----
>
> What version of SQR do you have?
>
> In 4 you can do
>
> let #months = datediff(datenow(), &EFFDT, 'month')
>
> --
> Cherno.
>
>  >>> Nhat-Ha Duong <nhat-ha.p.duong@BELLATLANTIC.COM> 11/02 5:38 PM >>>
> Is there a similar function to MONTHS_BETWEEN in SQR?  Here is what I
> tried to
> do:
>
> select
> ...
> EFFDT,
> ...
> from
> ....
> where
> ...
> and MONTHS_BETWEENS(CURRENT DATE, &EFFDT) <= #No_of_Month_Input
>
> Thanks,
> Nhat-Ha
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com