SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast Mary_Itohan's Avatar
    Join Date
    Dec 2004
    Location
    e-Planet
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL : how to calculate hours between 2 dates

    Looking for way&logic to calculate hours between 2 dates (Ticket.date_opened and Ticket.solved_date) but excluding weekends ?
    This function calculates hours only
    DATEDIFF(hour, Ticket.date_opened, Ticket.solved_date).

    Thanks!
    KISS - Keep It Simple Stupid

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    easiest way is to have a calendar table, with one row per day and weekends flagged somehow, best is by using the day of week from 1 through 7

    then your query is
    Code:
    select count(*)*24 as hours_between
      from calendar 
    inner
      join Ticket
        on calendar.daydate between Ticket.date_opened 
                                and Ticket.solved_date
     where calendar.dayofweek between 2 and 6
    if you were also to store business hours, then instead of count(*)*24 you could use sum(calendar.bushours)

    this would allow you to count hours for half holidays like christmas eve

    and of course you could flag holidays too, so as not to count them, by adding a flag to the table and another WHERE condition
    Code:
     where calendar.dayofweek between 2 and 6
       and calendar.holiday = 'N'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The following is a possible solution without messing around with the database.

    Code:
    <cfset RightNow = now()>
    <cfif isDefined("Form.OrderDate")>
        <cfset SomeDate = createDateTime(right(Form.OrderDate, 4), left(Form.OrderDate, 2), mid(Form.OrderDate, 4, 2), 0, 0, 0) />
        <cfif datePart("w", SomeDate) eq 1>
            <cfset SomeDate = createDateTime(datePart("yyyy", SomeDate), datePart("m", SomeDate), datePart("d", SomeDate) + 1, 0, 0, 0)>
        <cfelseif datePart("w", SomeDate) eq 7>
            <cfset SomeDate = createDateTime(datePart("yyyy", SomeDate), datePart("m", SomeDate), datePart("d", SomeDate) + 2, 0, 0, 0)>
        </cfif>
        <cfoutput>#datePart("w", SomeDate)#</cfoutput>
        <!---<cfset BusinessDays = datediff("d", SomeDate, RightNow)-(datediff("ww", SomeDate, RightNow)*2)>--->
        <cfset BusinessDays = datediff("h", SomeDate, RightNow)-(datediff("ww", SomeDate, RightNow)*48)>
        <cfoutput>Diff=#BusinessDays#</cfoutput>
    <cfelse>
    <html>
        <head>
        <title>Date diff no weekends</title>
        </head>
        
        <body>
            <form method="post">
                Order Date
                <input type="text" id="OrderDate" name="OrderDate" />
                <input type="submit" value="Submit">
            </form>    
        </body>
        
    </html>
    </cfif>


Bookmarks

Posting Permissions

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