Re: HOWTO specify an interval of 1 month

From: Markus Eltschinger <fake_address_at_home.com>
Date: Wed, 30 Jul 2003 14:44:29 +0200
Message-ID: <1059569074.158388@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_EN.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 Wed Jul 30 2003 - 07:44:29 CDT

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