Calculating how old a datetime value is taking working hours into account

Question

I'm creating an ASP.NET application (C#), I'm nearly finished however I have the following question.

Scenario

In my MSSQL table I have a datetime value i.e. 2015-05-06 13:38:17.000 I need to find out if this is over 6 hours old but I need to take into account the fact we are only working from 8:30-18:00 Monday to Friday.

I currently work out if an item is over 4 hours old (not taking working hours into account) using the following query:

SELECT COUNT(*) FROM Table WHERE [DateSubmitted] < DATEADD(HOUR,-4,GETDATE())

I've read up on how to calculate the working hours: Calculate business hours between two dates but I don't know how to apply this to what I want to do.

Any help would be appreciated - thank you.

In the post you reference; the StartDate would be 2015-05-06 13:38:17.000 and the FinishDate would be DateTime.Now() and alter WorkStart to 8:30 and WorkFinish to 18:00 ... as for converting a SQL 2008 stored function to MYSQL I don't know, but I don't think it would be that different. Take a look at http://stackoverflow.com/questions/14606900/mysql-create-a-simple-function — Paul Zahra, Jun 17 '15 at 09:24
Just use a T-SQL clause with a WHERE CASE Statements, here are some [examples](https://social.msdn.microsoft.com/Forums/en-US/ddf06f4b-53b6-4c44-80aa-a94b7ce10884/tsql-using-case-statement-in-where-clause-multiple-conditions?forum=transactsql) — Jeremy Thompson, Jun 17 '15 at 09:45

Heinz Siahaan · Accepted Answer · 2015-06-17T10:48:17.683

Just get the max date within business hour and do your query with that parameter

DECLARE @EndDate DATETIME, @StartBusinessDay DATETIME, @YesterdayEndBusinessDay DATETIME,@Interval DECIMAL(10,2)

SET @Interval=4*3600*-1

--set start period of business hour 
--you can change hard coded date to variable one
SELECT  @StartBusinessDay=CAST('2015-06-17 08:00:00' AS datetime),
@YesterdayEndBusinessDay =CAST('2015-06-16 17:00:00' AS datetime)

--get maximal date with basic calculation
SELECT @EndDate=DATEADD(ss,@Interval, GETDATE())

--if max date is not within business hour, do this
IF(@EndDate<@StartBusinessDay)
BEGIN
    DECLARE @Difference DECIMAL(4,2)

    --get the difference between result of basic calculation and start business hour
    SELECT @Difference=DATEDIFF(ss, @EndDate, @StartBusinessDay)

    --subtract it with initial interval
    SET @Difference=@Interval-@Difference;

    --get the new max date within business hour 
    SELECT @EndDate=DATEADD(ss,@Difference,@YesterdayEndBusinessDay)

    SELECT @EndDate
END

--query with max date within business hour
SELECT COUNT(1) FROM Table
WHERE [DateSubmitted] < @EndDate

Calculating how old a datetime value is taking working hours into account

1 Answers1