Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    1

    Unanswered: days and hours between 2 dates (minus weekends)

    Hi,

    I have a query that will give me the number of business days (taking away saturdays and sundays) between 2 dates... I need to extend this to give me the number of hours also (or fraction of a day) for more accuracy, can anyone help?

    The SQL I currently have is...

    HTML Code:
    SELECT count(*)
    FROM (SELECT rownum my_row, TO_DATE ('&&from_date', 'dd/mm/yyyy hh:mi:ss') + LEVEL - 1 mydate
                    FROM DUAL
              CONNECT BY LEVEL < =
                              TO_DATE ('&&to_date', 'dd/mm/yyyy hh:mi:ss')
                            - TO_DATE ('&&from_date', 'dd/mm/yyyy hh:mi:ss')
                            + 1
    	)
    	
       WHERE TO_CHAR (mydate, 'DY') NOT IN ('SAT', 'SUN')
    Any help on how to include hours also would be great!!

    Cheers,

    Jon

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm afraid I didn't quite understand the problem, but nevertheless: now you have days and want to have hours? As one day has 24 hours, I'd multiply [number of days * 24]. But it can't be that simple, can it?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    omg i love recursive logic

    not

    littlefoot, he wants to know the number of hours from 3 o'clock this afternoon until 7 in the morning next monday
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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