#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    1
    Rep Power
    0

    Ora-01847: Day Of Month Must Be Between 1 And Last Day Of Month


    My goal is to:

    A) extract subscriber numbers from TCME153.SUB then
    B) bounce those subscriber numbers off of a disconnect date i.e. DISCO_DTE_SBB then
    C) bounce A) and B) off of an item table i.e. ITV_ITEM_VALUE

    My hope is that the first query works as intended. Second and third queries throw error 'ORA-01847: DAY OF MONTH MUST BE BETWEEN 1 AND LAST DAY OF MONTH'. Please advise and respond - urgent.

    1) SELECT DISTINCT SUB_ACCT_NO_ITV
    FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB
    WHERE EXISTS (SELECT SBB_BASE.DISCO_DTE_SBB FROM (SELECT SUB
    FROM TCME153.SUB));

    2) SELECT DISTINCT SUB_ACCT_NO_ITV
    FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB
    WHERE EXISTS (SELECT SUB
    FROM TCME153.SUB)
    AND SBB_BASE.DISCO_DTE_SBB <> '0001/01/01';
    --ORA-01847: DAY OF MONTH MUST BE BETWEEN 1 AND LAST DAY OF MONTH

    3) SELECT DISTINCT SUB_ACCT_NO_ITV
    FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB
    WHERE SBB_BASE.DISCO_DTE_SBB IN (SELECT SUB
    FROM TCME153.SUB);
    --ORA-01847: DAY OF MONTH MUST BE BETWEEN 1 AND LAST DAY OF MONTH
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Cool


    If SBB_BASE.DISCO_DTE_SBB is of type "DATE", then do this:
    Code:
    ...
    AND SBB_BASE.DISCO_DTE_SBB <> TO_DATE('0001/01/01','YYYY/MM/DD');

    PS: Always compare dates to dates.

IMN logo majestic logo threadwatch logo seochat tools logo