MS SQL : how to calculate hours between 2 dates

Databases
1

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!

2

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

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

 where calendar.dayofweek between 2 and 6
   and calendar.holiday = 'N'
3

The following is a possible solution without messing around with the database.

<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>