# Difference Between Two Dates in Seconds Excluding Sunday and Out of Office Hours

Discussion in 'SQL Server 2008 General DBA Questions' started by jchmiel, Apr 21, 2011.

1. ### jchmielNew Member

I would like the Difference in Seconds between the Create Date and the Personal Response that would not include Sunday or any hours out side of 8AM to 8PM. Basically within working hours. I could do this manually outside SQL but it would be awesome if someone had a Statement that could calculate it. I know someone out there has had to do this but I cannot find the solution. Please help!
CURRENT SIMPLE STATEMENT
SELECT CREATE_DATE, PERSONAL_RESPONSE_SENT_DT, DATEDIFF(ss, CREATE_DATE, PERSONAL_RESPONSE_SENT_DT) AS DATEDIF, DATEPART(weekday, CREATE_DATE) AS CD_WKD, CONSUMER_ID FROM LEADS WHERE MONTH(PERSONAL_RESPONSE_SENT_DT) = 3 AND YEAR(PERSONAL_RESPONSE_SENT_DT) = 2011 ORDER BY PERSONAL_RESPONSE_SENT_DT DESC
SOME TABLE DATA
CREATE_DATE PERSONAL_RESPONSE_SENT_DT DATEDIF CD_WKD CONSUMER_ID
Mar 31 2011 07:44PM Mar 31 2011 07:47PM 210 5 173409
Mar 31 2011 06:04PM Mar 31 2011 06:21PM 1050 5 120497
Mar 31 2011 05:49PM Mar 31 2011 05:49PM 19 5 173403
HERE IS ODD DATA (The 27th is a SUNDAY...)
so the first row should be 145079sec - 3hours 34min (till 8PM) - 24hours(SUNDAY) - 8hours(till 8AM)
145079 - 12840 - 86400 - 28800 = 17039sec / 60 = 283.98min / 60 = 4.73hour response time
Mar 26 2011 04:34PM Mar 28 2011 08:52AM 145079 7 172994
Mar 27 2011 04:54PM Mar 28 2011 08:51AM 57406 1 173021
Mar 27 2011 08:44AM Mar 28 2011 08:50AM 86785 1 173012