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

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0

    Question ORA-01843: not a valid month


    This has to be a silly programming error...but at this point I can't see it.

    I have 2 cursors. Cursor 1 selects a number and 2 dates (values in the Table are in the format mm/dd/yyyy for both the columns).

    I pass these values to Cursor 2.

    v_promotion_id promotion.promotion_id%TYPE;
    v_promotion_start_date promotion.start_date%TYPE;
    v_promotion_end_date promotion.end_date%TYPE;

    CURSOR c_promotion_list IS
    (SELECT DISTINCT p.promotion_id,
    p.start_date - 5,
    p.end_date + 5
    FROM promotion p,
    promotion_list pl
    WHERE p.promotion_id = pl.promotion_id
    AND TRIM (pl.source_code) IS NOT NULL
    AND SYSDATE BETWEEN p.start_date AND p.end_date + 5);

    CURSOR c_trans_date_list (
    p_promotion_id promotion.promotion_id%TYPE,
    p_promotion_start_date promotion.start_date%TYPE,
    p_promotion_end_date promotion.end_date%TYPE
    ) IS
    SELECT DISTINCT th.transaction_date
    FROM transaction_header th,
    transaction_detail td,
    transaction_coupon tc
    WHERE th.transaction_id = td.transaction_id
    AND td.transaction_id = tc.transaction_id
    AND td.transaction_line_no = tc.transaction_line_no
    AND th.transaction_date BETWEEN TO_DATE (p_promotion_start_date - 5, 'mm/dd/yyyy') AND TO_DATE (p_promotion_end_date, 'mm/dd/yyyy')
    AND tc.coupon_code = TO_CHAR (p_promotion_id);

    OPEN c_promotion_list;

    LOOP
    FETCH c_promotion_list
    INTO v_promotion_id,
    v_promotion_start_date,
    v_promotion_end_date;

    EXIT WHEN c_promotion_list%NOTFOUND;

    OPEN c_trans_date_list (TO_CHAR (v_promotion_id), TO_DATE (v_promotion_start_date, 'mm/dd/yyyy'), TO_DATE (v_promotion_end_date, 'mm/dd/yyyy'));


    LOOP
    FETCH c_trans_date_list
    INTO v_date;

    EXIT WHEN c_trans_date_list%NOTFOUND;



    I am getting this error: ORA-01843: not a valid month

    Initially I was passing just the variables without To_date. But I got the same error.

    Please help!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0
    I tried all of the following:

    CURSOR c_promotion_list IS
    (SELECT DISTINCT p.promotion_id,
    to_date(p.start_date - 5, 'dd-mon-yy'),
    to_date(p.end_date + 5, 'dd-mon-yy')
    FROM promotion p,
    promotion_list pl
    WHERE p.promotion_id = pl.promotion_id
    AND TRIM (pl.source_code) IS NOT NULL
    AND SYSDATE BETWEEN p.start_date AND p.end_date + 5);

    and ...
    OPEN c_trans_date_list (TO_CHAR (v_promotion_id), TO_DATE (v_promotion_start_date, 'dd-mon-yy'), TO_DATE (v_promotion_end_date, 'dd-mon-yy'));

    and...
    OPEN c_trans_date_list (v_promotion_id, v_promotion_start_date, v_promotion_end_date);

    and...
    changed the v_promotion_start_date to varchar2 and tried passing as..
    OPEN c_trans_date_list (TO_CHAR (v_promotion_id), TO_CHAR (v_promotion_start_date, 'mm/dd/yyyy'), TO_CHAR (v_promotion_end_date, 'mm/dd/yyyy'));

    nothing worked
    What am I doing wrong?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0

    Talking Fixed it :)


    AND th.transaction_date BETWEEN TO_DATE (p_promotion_start_date - 5, 'mm/dd/yyyy') AND TO_DATE (p_promotion_end_date, 'mm/dd/yyyy')

    I don't need date conversion here!

IMN logo majestic logo threadwatch logo seochat tools logo