Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Angry Unanswered: Adding business days to functional field

    Hello once again,

    Well to be honest I really didn't want to use you guys before exhausting all angles. Well I have tried my best but still cannot come up with anything. I need to know how to add 7 business days to a field that is being converted from a function.

    So all I need to know is convert_utc_date(Create_Date,'EST') + 7 business days. How do I account for business days? I found this on the ask tom site but it's not working for me and I do not understand the logic here. Is there another easier way to add a number of business days to a date field in oracle. To show you the code that I found and applying it to my fields is below.

    Tom's Code

    select to_date(:dt,'dd-mon-yyyy')+trunc(:r/5)*7+mod(:r,5)+sign(greatest(0,mod(:r,5)+to_char(t o_date(:dt,'dd-mon-yyyy'),'d')-6))*2 new_dt

    My Code

    select to_date(convert_utc_date(Create_Date,'EST'),'dd-mon-yy') + trunc(7/5)*7+mod(7,5) +sign(greatest(0,mod(7,5))) + (((to_char(to_date(convert_utc_date(Create_Date,'E ST'),'dd-mon-yy'),'d')-6))*2) new_dt from chg_task;

    Now if I run
    select convert_utc_date(Create_Date,'EST'),
    select to_date(convert_utc_date(Create_Date,'EST'),'dd-mon-yy') + trunc(7/5)*7+mod(7,5) +sign(greatest(0,mod(7,5))) + (((to_char(to_date(convert_utc_date(Create_Date,'E ST'),'dd-mon-yy'),'d')-6))*2) new_dt from chg_task;

    I get records and they are all wrong i.e
    CreateDate=30-OCT-00
    NewDT=01-NOV-00

    This is not business days. Am I missing something. Any other suggestions experts. Thanks again.

  2. #2
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Tom's solution works fine

    Tom's solution works fine. But, I did find a syntax error when I copied and pasted from your post. The second TO_DATE has a misplaced space.
    'T O_DATE' should be TO_DATE


    SELECT TO_DATE(:dt,'dd-mon-yyyy')+TRUNC(:r/5)*7+MOD(:r,5)+SIGN(GREATEST(0,MOD(:r,5)+TO_CHAR(t o_date(:dt,'dd-mon-yyyy'),'d')-6))*2 new_dt
    FROM dual;



    SELECT TO_DATE('16-MAY-2006','dd-mon-yyyy')+TRUNC(4/5)*7+MOD(4,5)+SIGN(GREATEST(0,MOD(4,5)+TO_CHAR(to_ date('16-MAY-2006','dd-mon-yyyy'),'d')-6))*2 new_dt
    FROM dual
    /

    NEW_DT
    ----------
    05/22/2006

    SQL>
    Last edited by wrwelden; 05-16-06 at 17:00.

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Tom's solution works fine

    Tom's solution works fine. But, I did find a syntax error when I copied and pasted from your post. The second TO_DATE has a misplaced space.
    'T O_DATE' should be TO_DATE


    SELECT TO_DATE(:dt,'dd-mon-yyyy')+TRUNC(:r/5)*7+MOD(:r,5)+SIGN(GREATEST(0,MOD(:r,5)+TO_CHAR(t o_date(:dt,'dd-mon-yyyy'),'d')-6))*2 new_dt
    FROM dual;



    SELECT TO_DATE('16-MAY-2006','dd-mon-yyyy')+TRUNC(4/5)*7+MOD(4,5)+SIGN(GREATEST(0,MOD(4,5)+TO_CHAR(to_ date('16-MAY-2006','dd-mon-yyyy'),'d')-6))*2 new_dt
    FROM dual
    /

    NEW_DT
    ----------
    05/22/2006

    SQL>

  4. #4
    Join Date
    Apr 2006
    Posts
    140
    Actually that was my bad on the syntax as it is running when I select it with my tool. Sorry about that. As for you saying it is running fine.....then I am not sure why mine is not working. Here is a subset of my data with the following select
    select convert_utc_date(Create_Date,'EST'),
    to_date(convert_utc_date(Create_Date,'EST'),'dd-mon-yy') + trunc(7/5)*7+mod(7,5)
    +sign(greatest(0,mod(7,5))) + (((to_char(to_date(convert_utc_date(Create_Date,'E ST'),'dd-mon-yy'),'d')-6))*2) new_dt from chg_task;

    Can you see anything wrong. My variable is 7 days. I can't see what I am doing wrong but definitely the values returned for New_DT are wrong. Any other suggestions. Thanks again for the reply.


    CreateDate New_DT
    30-OCT-00 01-NOV-00
    30-OCT-00 01-NOV-00
    01-NOV-00 07-NOV-00
    01-NOV-00 07-NOV-00

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    Well I apologize mrwelden. I ran the select that you said was incorrect and applied my field that has a function and now it works. That doesn;t make sense. If it didn't recognize the t o_ date then it should have given me an error. Instead it returns results. I really thank you for having a look at this for me. Have a great day Mrwelden.

  6. #6
    Join Date
    Apr 2006
    Posts
    140
    Sorry experts,

    Can I reopen this issue. Now that is working correctly I am trying to add this formula to another forumla. When I run the below forumla I get ORA-01861:Literal does not match format string. Any ideas why?

    (CASE when convert_utc_date(Actual_End_date,'EST')
    between add_months(trunc(sysdate,'MM'),-11) and sysdate and (convert_utc_date(Actual_End_date,'EST') <= convert_utc_date(Planned_End_Date,'EST')) and (convert_utc_date(Planned_End_Date,'EST') >= TO_DATE(convert_utc_date(Create_Date,'EST'),'DD-MON-YY')+TRUNC(7/5)*7+MOD(7,5)+SIGN(GREATEST(0,MOD(7,5)+TO_CHAR(to_ date(convert_utc_date(Create_Date,'EST'),'DD-MON-YY'),'D')-6))*2) then 1 else 0
    End)MetLst12MthsPlanEDate

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    One other option you might want to consider (once you figure this out) is what you're supposed to do if there are holidays. Are they considered business days in your organization?

    I would also suggest turning this combination of functions into a PL/SQL function until you get the bugs worked out. Maybe seeing the data transform piece by piece will help you find where the problem is.

    -Chuck

  8. #8
    Join Date
    Apr 2006
    Posts
    140
    Hi Chuck,

    I appreciate the response. Well I wish I could figure it out. Working on it as we speak. There is just so much going on in this formula that I think I need another set of eyes. I realized the holiday part to and still see if that means anything to this company. Thanks again Chuck. Hopefully someone mught be able to help me on this one.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Hopefully someone mught be able to help me on this one.
    Not until/unless you learn & use CODE tags as described in the #1 Sticky Post
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Apr 2006
    Posts
    140
    Sorry Anacedent,

    I apologize for that. Could you please direct me to where the #1 Sticky post is. I am looking to find it so I can post the code properly. Thanks again.

  11. #11
    Join Date
    Jan 2004
    Posts
    492
    http://www.dbforums.com/misc.php?do=bbcode --or-- Just hit the quote button on my post and you can see how I used the code tags

    It ends up formatted like:
    Code:
    select sysdate
      from dual
     where text = 'MUCH MORE READABLE'
    Oracle OCPI (Certified Practicing Idiot)

  12. #12
    Join Date
    Apr 2006
    Posts
    140
    Thanks ss659. Hope I got you. Here goes.

    Code:
     select (CASE when convert_utc_date(Actual_End_date,'EST')
    between add_months(trunc(sysdate,'MM'),-11) and sysdate and (convert_utc_date(Actual_End_date,'EST') <= convert_utc_date(Planned_End_Date,'EST')) and (convert_utc_date(Planned_End_Date,'EST') >= TO_DATE(convert_utc_date(Create_Date,'EST'),'DD-MON-YY')+TRUNC(7/5)*7+MOD(7,5)+SIGN(GREATEST(0,MOD(7,5)+TO_CHAR(to_ date(convert_utc_date(Create_Date,'EST'),'DD-MON-YY'),'D')-6))*2) then 1 else 0
    End)MetLst12MthsPlanEDate from chg_task
    I hope this helps. Sorry experts.

  13. #13
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    A little different take on this issue.

    Create a calendar table with a field that defineds business days with a indicator (CAL_TEST)

    the do the following

    select * from (
    select q1.*, rank() over (partition by q1.work_date order by q1.calendar_date) rk
    from (select
    b.calendar_date work_date,
    l2.calendar_date
    from rxb0864.cal_test1 b
    left join (select * from rxb0864.cal_test where busDay = 1) l2
    on b.calendar_date < l2.calendar_date ) q1)q2
    where q2.rk = 7

    I think that this will work.

    Sorry code was wrong I pasted the wrong stuff.
    Last edited by rbackmann; 05-17-06 at 15:29.

Posting Permissions

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