Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Question Unanswered: Date fields and number fields

    Hello!

    I have a bit of a brain freeze, I am tryin to work out whether a job that was due to be completed by a particular date did or whether it was completed on time:

    The completion due on the job is calculated by priority is was set i.e

    1 week = 7 days
    3 months = 90 days and so on

    I kind of want to a sub select:

    (SELECT min(job.log_effective_date) + priority.no_days + priority.no_hours) as Comp Date,

    from....)

    then i can compare this to the actual comp date to see if the job was/was not completed on time. Does the above seem right? Thats what i would try?

    Lucy

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DATE datatype arithmetics operates in days:
    Code:
    SQL> select sysdate, (sysdate + 5) five_days_later from dual;
    
    SYSDATE    FIVE_DAYS_
    ---------- ----------
    22.07.2009 27.07.2009
    Therefore, you have to convert hours to days if you want the result to be correct:
    Code:
    SQL> select sysdate, (sysdate + 5 / 24) five_hours_later from dual;
    
    SYSDATE             FIVE_HOURS_LATER
    ------------------- -------------------
    22.07.2009 07:43:48 22.07.2009 12:43:48
    Moreover, 3 months is not 90 days. (Though, leap year may help to adjust 3 consecutive months to make 90 days, but that's an exception). If you want to work with months, use ADD_MONTHS function:
    Code:
    SQL> select sysdate, add_months(sysdate, 3) three_months_later from dual;
    
    SYSDATE    THREE_MONT
    ---------- ----------
    22.07.2009 22.10.2009

Posting Permissions

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