Calculate time difference between two business dates

JeffDun
JeffDun used Ask the Experts™
on
I need a SQL function that will calculate the elapsed time between two datetime values.  Also, I need to exclude saturdays and sundays and if possible, only include the times that are within the normal hours of operation( 8am-5pm).  The need output has to be split into days, hours, and minutes.  ex.  Days 5 : Hours 13 : Minutes 30.  Is this possible in SQL Server?  I know there is a similar function in Excel.  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
are there any time zone issues with this data (i.e. is 8am-5pm as seen in the dbms also 8am-5pm to all users ?)

additionally: please provide:
table name(s)
field names
field data types (or we will assume things like datetime)
sample data
otherwise: you will get generic answers using generic table/field names

Author

Commented:
I have a task table that contains datetime values for each task that has been completed.  I have mangaged to create a temp table that contains all of the tasks and datetimes in sequential order for each employee. I need the ability to get the time elapsed between two completion datetime values in the table by calling a sql function.  There are no time zone issues, 8am-5pm is the same for all users.  I need to be able to display the
elapsed time for multiple datetime combinations. I tried using DATEADD(SS,(DATEDIFF(SS,@date1,@date2)),'1900/01/01') but I'm not sure how I would elminate weekends and times that fall outside of the normal work hours.

Ex.
Task Table
task_ID   Completion_Date          
1       '2013-03-10 8:30:18.007'
2       '2013-03-15 10:00:18.007'
3       '2013-03-16 8:30:18.007'
4      '2013-03-18 8:30:18.007'
......

Execute a sql function to calculate elapsed time for all sections
getElapsedTime('2013-03-10 8:30:18.007','2013-03-15 10:00:18.007')
getElapsedTime('2013-03-16 8:30:18.007','2013-03-18 8:30:18.007')

Results
Elapsed time for task 1 to task 2
Days : hours : Minutes
Elapsed time for task 3 to task 4
Days : hours : Minutes
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
might I observe that you probably want the temp table structure like this:
employeeId, taskId, Commence_date, Completion_Date

tracking over rows through one field is possible but overly complicates your objective.
what is indicated above is that rows 1 & 2 belong to each other, and rows 3 & 4 belong to each other - by "sequential order" - that's not what I would recommend.

Is it possible to change the structure of this temp table?
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
the following code (from here) gives a basic structure for a "networkdays" calculation - but not (yet) the partial day portion of your question.
CREATE FUNCTION dbo.NetWorkDays (
 @StartDate DATETIME
 , @EndDate DATETIME
 )
RETURNS INT
AS
BEGIN
 DECLARE @result INT
  , @StartDate2 DATETIME
  , @EndDate2 DATETIME
  , @DateSwap1 DATETIME
  , @DateSwap2 DATETIME
  , @ReturnNegative BIT

 SET @DateSwap1 = @StartDate
 SET @Dateswap2 = @EndDate
 SET @ReturnNegative = 0

 IF @EndDate < @StartDate
 BEGIN
  SET @ReturnNegative = 1
  SET @StartDate = @Dateswap2
  SET @EndDate = @DateSwap1
 END

 SET @StartDate2 = dateadd(d, 8 - datepart(dw, @StartDate), @StartDate)
 SET @EndDate2 = dateadd(d, 1 - datepart(dw, @EndDate), @EndDate)
 SET @result = datediff(d, @StartDate2, @EndDate2) * 5 / 7 + datediff(d, @StartDate, @StartDate2) - 1 + datediff(d, @EndDate2, @EndDate) - CASE 
   WHEN datepart(dw, @StartDate) = 1
    THEN 1
   ELSE 0
   END - CASE 
   WHEN datepart(dw, @EndDate) = 7
    THEN 1
   ELSE 0
   END

 IF @ReturnNegative = 1
 BEGIN
  SET @result = @result * - 1
 END

 RETURN @result
END;

Open in new window

and there's scope for an improved method of weekend detection (using modulus) that could be incorporated.

Author

Commented:
Sorry, the sequential order in the table does not affect the query.  It's just an identity value.  In some cases, I will need to be able to calculate the time elapsed between tasks that do not occur in a sequence.  For example - If task 21 has not been completed, get the time elapsed between task 20 and task 22.  That's why I decided to create a function to be able to calculate the time elapsed between any two datetime values.
    I'm open to any table structure.  I just need to be able to calculate the elapsed time for two datetime values.  The results of the query will be in a Crystal Report.  I can arrange the results in the correct order on the report.

Author

Commented:
How would I convert the INT value from the example above into Days : hours : Minutes?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I have prepared an alternative function which adopts some of the previous function (which I didn't write) but works in seconds instead of days. The logic of this new function is to treat the time elements of a start date/time and a finish date/time separately, then any days between are calculated as before (multiplied by standard working seconds).
e.g.

a start at 13:00:00 is evaluated to 17:00:00 = 14400 seconds (4 hours)
a finish at 12:00:00 is evaluated from 08:00:00 = 14400 seconds (4 hours)

e.g:
start day1 13:00:00 = 14400 seconds (4 hours)
finish day2 12:00:00 = 14400 seconds (4 hours)
= 28800 seconds (8 hours)

start dayMon 13:00:00 = 14400 seconds (4 hours) on that Monday
finish dayTue 12:00:00 = 14400 seconds (4 hours) on that Tuesday
= 28800 seconds (8 hours)
+ 5 * 28800 seconds (5 * 8 hours) [Tue/Wed/Thu/Fri/Mon]
= 172800 seconds (48 hours)

HOWEVER.....

08:00:00 to 17:00:00 is actually 9 hours....
SO
a maximum of 28800 seconds (8 hours) per day is imposed, which is OK except that a start of 09:00:00 and finish of 17:00:00 on the same day is also 28800 seconds (8 hours) and no further deduction is made. Basically you MIGHT get an extra hour on the start day and/or an extra hour on the finish day because this 1 hour (probably lunch) isn't taken into account.

You should definitely take the time to look over the test results at this sqlfiddle link. There you will find some few deliberate test cases and some randomly generated data used to evaluate this new function's output. It is also compared to the former networkdays function (which is also compared to calculations done by Excel when generating the random data).

I have opted to return seconds from this new function which can be further calculated to days/hours as needed, like this:
, dbo.NetWorkSecs(start,finish) /(60.0*60.0)  as NetWorkHours

If you don't want seconds returned then the function itself can be modified to return decimal hours - this I leave to you.

Please do evaluate the function at sqlfiddle. Here's the current code
CREATE FUNCTION dbo.NetWorkSecs (
       @p_StartDate as DATETIME
     , @p_EndDate as DATETIME
     )
RETURNS INT
AS
BEGIN
 DECLARE
    @result       as INT  = 0
  , @v_StartDate  as DATE
  , @v_EndDate    as DATE
  , @v_StartDate2 as DATE
  , @v_EndDate2   as DATE
  , @v_StartTime  as TIME
  , @v_EndTime    as TIME
  , @v_ShiftStart as TIME = cast('08:00:00' as time)
  , @v_ShiftEnd   as TIME = cast('17:00:00' as time)
  , @v_ShiftSecs  as INT  = (8*60*60)
  , @v_OnStart    as INT
  , @v_OnEnd      as INT

IF @p_StartDate <> @p_EndDate /* i.e. no point doing more if they are equal */
  BEGIN
    IF @p_EndDate < @p_StartDate
         /* finish before start, so flip over the given dates for "sequence of event" consistency */
         BEGIN
          SET @v_StartTime = cast(@p_EndDate as TIME)
          SET @v_EndTime   = cast(@p_StartDate as TIME)
          SET @v_StartDate = dateadd(day,1,cast(@p_EndDate as date))   /* next day, because start time accounted for seperately */
          SET @v_EndDate   = dateadd(day,0,cast(@p_StartDate as date)) /* start of last day because finish time accounted for seperately */
         END
    ELSE
         BEGIN
          SET @v_StartTime = cast(@p_StartDate as TIME)
          SET @v_EndTime   = cast(@p_EndDate as TIME)
          SET @v_StartDate = dateadd(day,1,cast(@p_StartDate as date)) /* next day, because start time accounted for seperately */
          SET @v_EndDate   = dateadd(day,0,cast(@p_EndDate as date))   /* start of last day because finish time accounted for seperately */
         END
         
        IF @v_StartDate = @v_EndDate /* i.e. after taking time into accout there are no whole days between start and finish times */
          BEGIN
             SET @v_OnStart = datediff(second,@v_StartTime ,@v_ShiftEnd)       /* seconds on start date within shift */
             SET @v_OnEnd   = datediff(second,@v_ShiftStart,@v_EndTime)        /* seconds on end date within shift */
             SET @result =   CASE
                                when @v_OnStart > @v_ShiftSecs then @v_ShiftSecs when @v_OnStart > 0 then @v_OnStart else 0
                             END
                           + CASE
                                when @v_OnEnd > @v_ShiftSecs then @v_ShiftSecs when @v_OnEnd > 0 then @v_OnEnd else 0
                             END
             END
        ELSE
          BEGIN
             SET @v_OnStart = datediff(second,@v_StartTime ,@v_ShiftEnd)       /* seconds on start date within shift */
             SET @v_OnEnd   = datediff(second,@v_ShiftStart,@v_EndTime)        /* seconds on end date within shift */
             /* the following logic from source 'networkdays' function, results are consistent with Excel networkdays */
             SET @v_StartDate2 = dateadd(d, 8 - datepart(dw, @v_StartDate), @v_StartDate)
             SET @v_EndDate2 = dateadd(d, 1 - datepart(dw, @v_EndDate), @v_EndDate)
             SET @result = (
                            (  datediff(d, @v_StartDate2, @v_EndDate2) * 5 / 7 
                             + datediff(d, @v_StartDate, @v_StartDate2) - 1 
                             + datediff(d, @v_EndDate2, @v_EndDate) 
                             - CASE 
                                   WHEN datepart(dw, @v_StartDate) = 1
                                    THEN 1
                                   ELSE 0
                                   END 
                             - CASE 
                                   WHEN datepart(dw, @v_EndDate) = 7
                                    THEN 1
                                   ELSE 0
                                   END)
                           * @v_ShiftSecs)
                           /* above we multiply by shift seconds for each intervening work day, and below, add seconds of first and last days) */
                           + CASE
                                when @v_OnStart > @v_ShiftSecs then @v_ShiftSecs when @v_OnStart > 0 then @v_OnStart else 0
                             END
                           + CASE
                                when @v_OnEnd > @v_ShiftSecs then @v_ShiftSecs when @v_OnEnd > 0 then @v_OnEnd else 0
                             END
           END
   END
 RETURN @result
END
;

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Oh, and also note, if the provided dates are 'backward' (finish is before start) the new function (NetWorkSecs) simply flips these over. This means the new function does not return a negative number of seconds. The logic to this is that we are evaluating start times and finish times within a day (to the second), so it is important to begin at the start and move forward over time until the end. i.e. there is only one logical sequence here. IF the business logic required a deduction of hours for some reason, then the functions output can be multiplied by -1

Also, there is no accommodation for holidays (at all), just Sat/Sun. (& Here I have NOT attempted revise the previous logic of NetWorkDays (which I didn't write)). Ideally this weekend logic would be independent of dbms settings and even better would be the ability to nominate which days of the week are excluded - but neither has been attempted.

This new function can be easily tweaked for different day shifts such as 09:00 to 18:00 simply by changing the variable values.  BUT it's worth mentioning that this function isn't suited to shifts that span a date (e.g. a 'night shift' starting 21:00 and finishing 06:00 the next day), this would require additional logic.

[edit, aded excel file for reference, data is random!]
Q-28064978-networksecs-randomdat.xlsx
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
NetWorkDays function from:
http://www.sqlservercentral.com/Forums/Topic14491-23-1.aspx#bm1362656

by: davidcriley      (citing  Patrick Jasinski as originator)

Author

Commented:
Ok, I will test the function and see if the solutions works for me.  Also, how do I convert seconds into days, hours, and minutes?
Thanks
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
?!!? (surprise)
the algorithm from seconds to higher units is pretty simple

minutes = seconds/60
hours = seconds/60/60
days = seconds/60/60/24

a scalar function can only return one value, plus you will want to avoid calling the function multiple times. so 'nest' your query, e.g.

select
  start
, finish
, networksecs/60 as networkminutes
,etc.
from (
           select
             start
           , finish
           , dbo.NetWorkSecs(start,finish) as networksecs
          from tasks
        ) as derived

The function could probably be revised to return a "time" type...
then you could use:
, datepart(day,networktime) as days
, datepart(hour,networktime) as hours
, datepart(minute,networktime) as minutes

there's a bucket load of voluntary work for these points
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
actually returning a time isn't possible, it would have to be a datetime and that's going to be complex for you to interpret, because the number of days would be measured from 1900-01-01

it will be much simpler to return a numeric, it could be minutes instead of seconds
nb: you can aggregate a numeric so calculating totals in much easier this way.

does it absolutely have to be displayed as days/hours/minutes?

why not just decimals hours? (seconds/3600)
e.g 101.55 hours
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
OK, forgetting the last couple of comments, if the function stays as is (returns seconds), the thing to remember is a "day" is actually represented by 8*60*60 = 28800 seconds, so the following query will report hours, days, minutes using that function:
select
  start
, finish
, (networksecs/28800) as days
, ((networksecs - ((networksecs/28800)*28800))/3600) as hours
, ((networksecs - (((networksecs/28800)*28800) + ((networksecs - ((networksecs/28800)*28800))/3600)*3600)) /60) as minutes
from (
           select
             start
           , finish
           , dbo.NetWorkSecs(start,finish) as networksecs
          from tasks
        ) as derived
;

Open in new window

see it at:
http://sqlfiddle.com/#!3/42277/3 (look for the second set of results)

all of this would be marginally simpler if the function returned minutes

Author

Commented:
First, thanks for all of the help, I'm really learning a lot.  The function does not have to return seconds, It can return minutes.  I just need the final output to display days, minutes, and hours.  If the function returns minutes, would i still need a nested query?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
a scalar function can only return one value. No exceptions.

To display 3 values from the function would require either:
a. calling the function 3 times, or
b. nesting so the single returned value can be reused

b. is better for performance

so, it will not matter if the function returns minutes, seconds or datetime you would still need to interpret the result for display into 3 seperate parts.

Author

Commented:
Sorry, I was wondering if there was a way to modify the function to return a varchar instead of an Int value with all three values.  Something like the following:

Select @ReturnValue = CONVERT(VARCHAR(3),(networksecs/28800)) + 'Days '
+ CONVERT(VARCHAR(3),((networksecs - ((networksecs/28800)*28800))/3600))+ 'Hours '
...... 'Minutes'

Would something like that work?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
you could not aggregate or sort the output if a varchar
I did consider it but would not recommend it

you are free to try it of course, in theory yes it can be done - just not sure its wise

Author

Commented:
Ok, I would not need to aggregate and the I would order the task by the task ID on the report.  Why would the report be simpler if I used minutes?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
the function is only slightly simpler to use in the query if it returns minutes

IF you relented on display by days/hours/minutes, and simply presented decimal hours that would be simpler....

otherwise I don't understand the question

I am not a development shop.... the function COULD return varchar in days/hours/minutes and I have shown you how that is possible also

what next?

Author

Commented:
I haven't been working with SQL that long and just needed more clarification on why one method was simpler than the other.  When I convert the seconds that are being returned from the code above, I'm still not receiving the expected results.

For example.  If I calculate the elapsed time from 2012-06-28 10:37:14.997 to 2012-07-11 08:31:02.813, I receive 12Days 21Hours 53Minutes.  However, If I use the code above(excluding weekends, including 8-5 work hours), I receive 284040 for the totalSeconds.  When I convert the total seconds to day, hours, minutes, I receive 3 days, 78 hours, and 4734 minutes.   The days are way off even with weekends excluded.  Is there a way to get the amount of days, hours, and minutes from the total seconds and have it display like 12Days 21Hours 53Minutes?

select @totalSeconds/60 as minutes  -4734
select @totalseconds/60/60 as hours  -78
select @totalSeconds/60/60/24 as days   --3
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I corrected myself here you are using "standard" formula for seconds to higher units - but the function is using "working days" so the number of seconds in a "working day" is 1/3 of the seconds in 24 hours.

The correct calculations are these:
declare @networksecs as int = 284040

select
  (@networksecs/28800) as days
, ((@networksecs - ((@networksecs/28800)*28800))/3600) as hours
, ((@networksecs - (((@networksecs/28800)*28800) + ((@networksecs - ((@networksecs/28800)*28800))/3600)*3600)) /60) as minutes

DAYS HOURS MINUTES
9    6     54

Open in new window

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
Hello, hope you haven't pulled your hair out... I have another approach for you, this time it returns a string of Days Hours minutes for you.

It is an entirely new function which is better suited to calculation of work hours wthin a work day. Output looks like this:
WORK DAYS HOURS MINUTES      TOTAL WORK MINUTES    DECIMAL WORK HOURS
0d:0h:0m                     0                     0
1d:0h:0m                     480                   1
0d:5h:0m                     300                   0.625
0d:4h:0m                     240                   0.5
0d:7h:0m                     420                   0.875
6d:4h:45m                    3165                  6.59375
8d:0h:0m                     3840                  8
21d:0h:13m                   10093                 21.027083
15d:4h:14m                   7454                  15.529166

Open in new window

You use it as follows, there are 2 basic options, 'd:h:m'  or total minutes. with Total minutes you can cast returned value for other uses (e.g. third column above. Here's what it loos like in a query:
select
  start
, finish
, dbo.GetWorkingStr(Start,Finish,9,17,'d:h:m')                    as [Work Days Hours Minutes]
, dbo.GetWorkingStr(Start,Finish,9,17,'min')                      as [Total Work Minutes]
, cast(dbo.GetWorkingStr(Start,Finish,9,17,'min') as decimal)/480 as [Decimal Work Hours]
from tasks

Open in new window

There are a total of 5 parameters, and all must be provided. A Start datetime, a finish datetime, work start hour (int, defualt 9), work finish hour (int, default 17),  style (defaults to 'd:h:m'). So here is the new function. Note it is derived from work by Arjun Khosla, I have made notes within the code on source and changes made to suit this question:
Create Function GetWorkingStr(
  @StartDate DateTime
, @EndDate DateTime
, @StartHour int     = 9        /* Start of business hours (as an argument) */
, @EndHour int       = 17       /* End of business hours (as an argument) */
, @style varchar(10) = 'd:h:m'  /* provide back days:hours:minutes, else number of seconds as varchar */
) 
Returns Varchar(24)
AS
/*
origin:
http://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates
Arjun Khosla
Sep 5 '12 at 7:57

Modified, PortetPaul Experts-Exchange.com for question
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28064978.html

aa. name change to NetWorkStr
a. removal of holiday table, code commented out
b. all comments use slash*, and some other cosmetics
c. altered return type (regretably) to varchar, with logic to return days/hours/minutes, or number of minutes (as varchar)
d. moved some variables into parameters

USAGE
, dbo.GetWorkingStr(Start,Finish,9,17,'d:h:m')                    as [Work Days Hours Minutes]
, dbo.GetWorkingStr(Start,Finish,9,17,'min')                      as [Total Work Minutes]
, cast(dbo.GetWorkingStr(Start,Finish,9,17,'min') as decimal)/480 as [Decimal Work Hours]


*/
Begin
    Declare @WorkMin int    = 0   /* Initialize counter */
    Declare @Reverse bit          /* Flag to hold if direction is reverse */
    /* Declare @Holidays Table (HDate DateTime) */   /*   Table variable to hold holidayes */
    Declare @ndays    int   = 0
    Declare @nhours   int   = 0
    Declare @nminutes int   = 0
    Declare @provide  varchar(80)

    /* If dates are in reverse order, switch them and set flag */
    If @StartDate>@EndDate 
    Begin
        Declare @TempDate DateTime=@StartDate
        Set @StartDate=@EndDate
        Set @EndDate=@TempDate
        Set @Reverse=1
    End
    Else Set @Reverse = 0

    /* Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema) */
    /* Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE=@Country and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0) */

    If DatePart(HH, @StartDate)<@StartHour  Set @StartDate = DateAdd(hour, @StartHour,    DateDiff(DAY, 0, @StartDate)) /* If Start time is less than start hour, set it to start hour */
    If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) /* If Start time is after end hour, set it to start hour of next day */
    If DatePart(HH, @EndDate)>=@EndHour+1   Set @EndDate   = DateAdd(hour, @EndHour,      DateDiff(DAY, 0, @EndDate))   /* If End time is after end hour, set it to end hour */
    If DatePart(HH, @EndDate)<@StartHour    Set @EndDate   = DateAdd(hour, @EndHour-24,   DateDiff(DAY, 0, @EndDate))   /* If End time is before start hour, set it to end hour of previous day*/

    If @StartDate>@EndDate Return 0

    /* If Start and End is on same day */
    If DateDiff(Day,@StartDate,@EndDate) <= 0
    Begin
        If Datepart(dw,@StartDate) > 1 And DATEPART(dw,@StartDate) < 7  /*  If day is between sunday and saturday */
            /* If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 */ /*  If day is not a holiday */
                If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) /*  Calculate difference */
            /* Else Return 0 */
        Else Return 0
    End
    Else Begin
        Declare @Partial int = 1   /*  Set partial day flag */
        While DateDiff(Day,@StartDate,@EndDate) > 0   /*  While start and end days are different */
        Begin
            If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7    /*   If this is a weekday */
            Begin
                If 1 = 1 /* due to removal of holiday (see next line), leave 1=1 in lieu */ 
                         /* (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 */ /*  If this is not a holiday */
                Begin
                    If @Partial=1  /*  If this is the first iteration, calculate partial time */
                    Begin 
                        Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
                        Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) 
                        Set @Partial=0 
                    End
                    Else Begin      /*  If this is a full day, add full minutes */
                        Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60        
                        Set @StartDate = DATEADD(DD,1,@StartDate)
                    End
                End
                Else Set @StartDate = DATEADD(DD,1,@StartDate)  
            End
            Else Set @StartDate = DATEADD(DD,1,@StartDate)
        End
        If Datepart(dw,@StartDate) > 1 And DATEPART(dw,@StartDate) < 7  /*  If last day is a weekday */
            /* If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 */  /*  And it is not a holiday */
            If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
    End 
    
    if @style = 'd:h:m'
    Begin
        Set @ndays    = (@WorkMin / ((@EndHour - @StartHour)*60))                 /* number of whole working days */
        Set @nhours   = (@WorkMin - (@ndays * ((@EndHour - @StartHour)*60)))/60                /* number of whole working hours, whole days deducted */
        Set @nminutes = (@WorkMin - ((@ndays * ((@EndHour - @StartHour)*60)) + (@nhours * 60)) ) 
        
        Set @provide = cast(@ndays as varchar) + 'd:' + cast(@nhours as varchar) + 'h:' + cast(@nminutes as varchar) + 'm'
    End
    Else Set @provide = cast(@WorkMin as varchar)
    
    /* If @Reverse = 1 Set @WorkMin = -@WorkMin */ /* original reverse logic not used */
    Return @provide /* original returned  @WorkMin */
End
;

Open in new window

Note the use of a work day using 9 and 17. This function is still not capable of dealing with a "lunch period" so the logic is to use an unbroken 8 hour period. This is better then the earlier approach and should result in an accurate final amount even though the real start time may be 08:00.

This function may be seen against some test data at http://sqlfiddle.com/#!6/b120c/9

Oh, and it can also be used with defaults, like this:
select
 dbo.GetWorkingStr(Start,Finish,default,default,default) as [Work Days Hours Minutes]
from a_table

Author

Commented:
You sir are amazing!!! I wish I could give you a ton of points for this.  Thanks for helping me through this.  I've learned a lot in the process.

Author

Commented:
Excellent solution.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I am truly pleased to hear you have a solution, it took a while but think it was worth it in the end.

Cheers.
Paul
Function seem to fail in first hour after end of working period.
Both for start and end date.

declare @dateFrom as datetime
declare @dateUntil as datetime

set @dateFrom = {ts '2016-09-08 10:00:00'}
set @dateUntil = {ts '2016-09-08 17:10:00'}

select cast(dbo.GetWorkingStr(@dateFrom,@dateUntil,9,17,'min') as decimal) / 60

results in 7.166666 (should be 7.000000)

set @dateFrom = {ts '2016-09-08 10:00:00'}
set @dateUntil = {ts '2016-09-08 18:10:00'}

select cast(dbo.GetWorkingStr(@dateFrom,@dateUntil,9,17,'min') as decimal) / 60

results in 7.000000 (is correct)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial