Adding business hours to a datetime field

Transact-SQL
1

Hi All,

I want to add business hours to datetime field(Ticket_Raised) and my business hours are 08:00 to 17:00.
Few conditions we keep in mind:
1)If any ticket raised below 08:00 then we should consider that as 08:00 on same day
2) If any ticket raised after 17:00 then we have to consider ticket raised date as next day 08:00
3) Also we need to exclude week ends and Holidays from this

Please help....... Thanks in advance ..Below is the sample table:
Ex: Ticket_Raised BusinessHours TargetDate
2016-02-21 06:24:23:6754 24 ?
2016-04-15 18:23:12:9876 48 ?
2016-01-02 11:13:16:1483 12 ?

2

How do you determine if a given day is a business day? The recommended way of doing it is with a calendar table.

What are the two columns BusinessHours and TargetDate?

If you just want to push everything from 5:00 PM to 8:00 AM next day to be at 8:00 AM next day, without regard to whether it is a holiday/weekend, you can do the following.

SELECT
	CASE 
		WHEN CAST(TicketRaised AS TIME) < '08:00:00' THEN 
			DATEADD(HOUR,8, DATEADD(DAY,DATEDIFF(DAY,0,TicketRaised),0))
		WHEN CAST(TicketRaised AS TIME) > '17:00:00' THEN 
			DATEADD(HOUR,32, DATEADD(DAY,DATEDIFF(DAY,0,TicketRaised),0))
		ELSE TicketRaised
		END 
FROM 
	YourTable

If you have a calendar table, then you would use that to push the date to the next business day if the result of this query happens to be on a holiday.

Even without