Re: HOWTO specify an interval of 1 month - 1 day ?

That's what I wanted !!

Thanks a lot Markus !!

Philippe

"Markus Eltschinger" <fake_address_at_home.com> wrote in message news:1059569074.158388_at_exnews...
> Philippe,
>
> when doing date arithmetics, please keep in mind that the following two
SQLs doesn't
> return the same result (as probably expected). The second SQL gives Oracle
error
> "ORA-01839: date not valid for month specified".
>
> SELECT ADD_MONTHS(DATE'2003-07-31', -1) FROM DUAL;
> SELECT DATE'2003-07-31' - NUMTOYMINTERVAL(1,'MONTH') FROM DUAL;
>
> Even if I didn't solve your problem, here's a tiny SQL which may help
anyway. Please
> consider,
> RANGE expects only numeric values, not dates. Provided you have a
continuous and
> complete
> set of dates, you can calculate the number of days between two dates and
use this
> difference in
> the RANGE statement, as I did in my example below:
>
> CREATE TABLE T_DUMMY
> AS
> SELECT CAST(DATE'2003-05-31' + ROWNUM AS DATE) AS DATE_COL
> FROM ALL_OBJECTS
> WHERE ROWNUM <= 120
> /
> SELECT DATE_COL,
> DATE_COL_START
> FROM (SELECT DATE_COL,
> FIRST_VALUE(DATE_COL) OVER (ORDER BY DATE_COL ASC
> RANGE (DATE_COL -
ADD_MONTHS(DATE_COL, -1) - 1)
> PRECEDING) AS DATE_COL_START
> FROM T_DUMMY)
> WHERE DATE_COL > DATE'2003-07-01'
> ORDER BY DATE_COL
> /
>
> Kind regards,
> Markus Eltschinger
> ______________________________
> Swisscom IT Services Ltd
> Data Warehouse Development
> 1752 Villars-Sur-Glāne FR
> Switzerland
>

http://www.swisscom.com/it/content/e-BusinessSolutions/crmdwh7/bidwh/index_E N.html
>
>
>
> "Philippe" <DONT_S_P_A_M_ME_pga_at_bsb.DOT.com> wrote in message
> news:bg7ntk$bcs$1_at_reader10.wxs.nl...
> > Yes I tried that.
> >
> > The problem is that YEAR TO MONTH and DAY TO SECOND are completely
different
> > data types...
> >
> > My problem is that a slinding window of ONE month is in fact 1 month + 1
day
> > (the one of the current day) i.e. on the 25/07/2003 the interval ranges
from
> > 25/06/2003 to 25/07/2003 BUT I need an interval from 26/06/2003 to
> > 27/07/2003
> >
> >
> > "LKBrwn_DBA" <member30625_at_dbforums.com> wrote in message
> > news:3162890.1059508519_at_dbforums.com...
> > >
> > > But did you try:
> > >
> > > RANGE (NUMTOYMINTERVAL(1,'month') + NUMTODSINTERVAL(1, 'day'))
PRECEDING
> > >
> > > --
> > > Posted via http://dbforums.com
> >
> >
>
>
Received on Thu Jul 31 2003 - 01:36:32 CDT