ORA-1847 day of month must be between 1 & last day of month
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORA-1847 day of month must be between 1 & last day of month

  1. #1
    Join Date
    Aug 2000
    Posts
    68

    ORA-1847 day of month must be between 1 & last day of month

    Hello

    Im trying to debug a troublesome trigger.

    In my when clause im using this type of logic....

    nvl(old.cust_flag, 'z') <> nvl(new.cust_flag,'z') or
    nvl(old.last_updated_by, 99999) <> nvl(new.last_updated_by,99999) or

    etc

    but im having problems with the date format by this i mean

    nvl(old.last_update,'9999') <> nvl(new.last_update,'9999')

    Table Struct

    cust_flag = VARCHAR150
    last_updated_by = Number 15
    last_update = Date




    The problem is prob the format (ie '9999) above) but im not too sure what to put in when referencing a date???

    I really hope someone can help...

    Thanks in Advacne to anyone who can...
    Carpe Diem

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hello,
    If the second argument does not the same datatype as the first argument, it must be converted. Obviously, '9999' is not a valid date.

    I believe you can use sysdate here:
    nvl(old.last_update, sysdate+1) <> nvl(new.last_update, sysdate+1)

    ... quam minimum credula postero.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by ales
    I believe you can use sysdate here:
    nvl(old.last_update, sysdate+1) <> nvl(new.last_update, sysdate+1)
    Since the original poster stated that his code is PL/SQL (trigger), I wouldn't use sysdate as suggested in the above code! If both :NEW.last_update and :OLD.last_update are NULL, then the above condition will most of the time result in FALSE, but sumetimes it will result in TRUE!!!!!

    Better use some constant date instead of SYSDATE, like:

    NVL(:OLD.last_update, TO_DATE(1,'J')) <> NVL(:NEW.last_update, TO_DATE(1,'J'))
    Last edited by jmodic; 01-10-2003 at 04:05 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Brabas wrote that he's using that in the WHEN clause of the trigger, not in the trigger body. The WHEN clause is a boolean SQL expression, not a PL/SQL code. The SYSDATE function is evaluated only once in a SQL expression, thus it's safe to use it in the manner I suggested.
    Please correct me if I'm wrong.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yep, you are righ! I missed that "small" detail about code being part of the WHEN trigger clause (that's why I used colons before NEW and OLD).

    In case of WHEN condition you are correct - sysdate is evaluated only once per expression, so your code is perfectly OK.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width