Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: problem with query Oracle

    Hi,
    I've table TAB_DATE:

    COD_ID............DATE_C..............DATE_R
    1................3/3/2005.............1/3/2005
    2................7/3/2005.............1/3/2005
    3................7/3/2005.............3/3/2005
    4................9/3/2005.............1/3/2005
    5................19/3/2005............14/3/2005
    6................28/2/2005.............7/2/2005
    7................15/3/2005.............9/3/2005
    8................15/3/2005.............1/3/2005
    9................15/3/2005............11/3/2005


    I want to create an Oracle query, so that the difference in time should be based on business day. (i.e. Saturday and Sunday must be excluded.)

    for example DATE_C = 15/3/2005
    DATE_R = 11/3/2005
    difference = 2

    DATE_C = 15/3/2005
    DATE_R = 9/3/2005
    difference = 4

    DATE_C = 15/3/2005
    DATE_R = 1/3/2005
    difference = 10

    DATE_C = 28/2/2005
    DATE_R = 7/2/2005
    difference = 15



    if I run:
    SELECT to_date( DATE_C,'dd/mm/yyyy') DATE_C
    FROM TAB_DATE
    where rownum=1

    COD_ID............DATE_C
    1................3/3/0005

    or

    SELECT to_date( DATE_C,'dd/mm/yy') DATE_C
    FROM TAB_DATE
    where rownum=1

    COD_ID............DATE_C
    1................3/3/2005


    I tried this query:

    select COD_ID,DATE_C,DATE_R,trunc((to_date(DATE_C,'dd/mm/yy') -
    to_date(DATE_R,'dd/mm/yy')+1)/7)*5 +
    length(replace(substr('012345001234560',to_char(to _date(DATE_R,'dd/mm/yy'),'d'),
    mod(to_date(DATE_C,'dd/mm/yy') - to_date(DATE_C,'dd/mm/yy')+1,7)),'0','')) difference
    from tab_date

    but I get this output:


    COD_ID............DATE_C..............DATE_R...... .......DIFFERENCE
    1................3/3/2005.............1/3/2005...............2
    2................7/3/2005.............1/3/2005...................
    3................7/3/2005.............3/3/2005...............4
    4................9/3/2005.............1/3/2005...............6
    5................19/3/2005............14/3/2005..............4
    6................28/2/2005.............7/2/2005.................
    7................15/3/2005.............9/3/2005.................
    8................15/3/2005.............1/3/2005.................
    9................15/3/2005............11/3/2005..............3

    or

    ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'

    and I tried:

    select COD_ID,DATE_C,DATE_R,trunc((to_date(DATE_C,'dd/mm/yyyy') -
    to_date(DATE_R,'dd/mm/yyyy')+1)/7)*5 +
    length(replace(substr('012345001234560',to_char(to _date(DATE_R,'dd/mm/yyyy'),'d'),
    mod(to_date(DATE_C,'dd/mm/yyyy') - to_date(DATE_C,'dd/mm/yyyy')+1,7)),'0','')) difference
    from tab_date

    but I get this output:

    COD_ID............DATE_C..............DATE_R...... .......DIFFERENCE
    1................3/3/2005.............1/3/2005...............3
    2................7/3/2005.............1/3/2005...................
    3................7/3/2005.............3/3/2005...............3
    4................9/3/2005.............1/3/2005...............7
    5................19/3/2005............14/3/2005..............5
    6................28/2/2005.............7/2/2005..............16
    7................15/3/2005.............9/3/2005.................
    8................15/3/2005.............1/3/2005..............11
    9................15/3/2005............11/3/2005..............3


    How Can I write my query to get the difference between date without Saturday and Sunday?

    Thanks in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

Posting Permissions

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