Hmmmm, I must be doing something wrong then, as the code you have described is exactly what I am doing. Initially I was using a view and passing these parameters to the function. Doing this I received this error:
Msg 8115, Level 16, State 2, Procedure CalcTimeBetweenTwoDates, Line 35
Arithmetic overflow error converting expression to data type datetime.
I thought this might be due to the fact that I was using a view so I inserted the values into a table. I then used this code:
where dbo.CalcTimeBetweenTwoDates(TicketCreatedDate,StatusChangeTime) > 4
order by id
but I get the same exact error message.
So I went in and modified my table. I only put one row in the table and everything worked fine. I then put a second row into the table with the EXACT same data. This also worked fine. I then added a row with DIFFERENT data at which time I got the above error.
I have modified your code to remove the lunch hour and I am pretty sure I caused the problem by doing this. I have attached the code for dbo.CalcTimeBetweenTwoDates . I can also add all the other functions if you need me to. Once again your help in this is GREATLY appreciated.