DateDiff using office hours only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DateDiff using office hours only

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-09-04 : 10:40:52

I've got these datetimes in my table: dateSubmitted and dateActioned.

I need a report of how many hours between the two - but I only want to count "working hours"

working hours are Monday-Friday 0900-1700

Any ideas how this could be achieved?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 11:05:37

Yes.
Start with dateSubmitted and check it is on weekend. if it is, forward the data to monday 0900.
If it is not, subtract datesubmitted from 1700 hours.

Now do the same with dateActioned.
If on weekend, rollback date to friday 1700. if not on weekend, substract 0900 hours from dateActioned.

Now last take the number of full days between (datesubmitted + 1) and (dateactioend - 1). subtract two days for every week difference you find.
Now multiple 8 hours with the number of remaining days and add the two previous hours you calculated.

You may have to check that dateSubmitted and dateActioned are not the same day.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 11:17:13

Here is a starter

CREATE FUNCTION dbo.fnWorkHours
(
	@StartDate DATETIME,
	@EndDate DATETIME)
RETURNS FLOAT
AS
BEGIN
	RETURN	CAST(
			(DATEDIFF(DAY, @StartDate, @EndDate) + 1)
			- (DATEDIFF(WEEK, @StartDate, @EndDate) * 2) 
			- (CASE WHEN DATENAME(WEEKDAY, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
			- (CASE WHEN DATENAME(WEEKDAY, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
	                - CASE WHEN DATENAME(WEEKDAY, @StartDate) IN ('Saturday', 'Sunday') THEN 0
				ELSE CAST(CONVERT(CHAR(12), @StartDate, 114) AS DATETIME) END
			- CASE WHEN DATENAME(WEEKDAY, @EndDate) IN ('Saturday', 'Sunday') THEN 0 
				ELSE 1.0 - CAST(CONVERT(CHAR(12), @EndDate, 114) AS DATETIME) END
	        AS FLOAT) * 24
END

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources