InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 09:46:54 GMT20http://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxSorry, just got back. I didn't really read through your second post, but I think this is right/close/gives you an idea. Also, SQL 2008 R2 makes it easier.[code="sql"]DECLARE @timeend TIME = '17:00'DECLARE @timestart TIME = '08:30'DECLARE @daylength INT = DATEDIFF(mi, @timestart, @timeend)DECLARE @StartDate DATETIME= '2012-05-10 8:30:00.000';DECLARE @EndDate DATETIME= '2012-05-14 15:00:00.000';SELECT (DATEDIFF(d, @StartDate, @enddate) * @daylength) --TotalMinutes, - (DATEDIFF(week, @StartDate, @enddate) * 2 * @daylength) --Weekend - (@daylength - DATEDIFF(mi, @timestart, CAST(@EndDate AS TIME))) --LastDay - DATEDIFF(mi,@timeend, CAST(@startdate AS TIME) ) -- First Day[/code]Always remember to test.Thu, 17 May 2012 15:03:09 GMTObshttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspx[quote][b]mota7128 (5/17/2012)[/b][hr]Hi Guys,I am using SQL 2008 R2, and I do thank all of you guys, REALLY love you.Much thanks for you "Lynn Pettis", your solution is awesome for me and I think that I can amend it to exclude Saturday and SundayBest Regards,Hatem[/quote]Look close, I did that already (hopefully) in the code I just posted. Be sure to test it in your environment.Thu, 17 May 2012 12:05:26 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxHi Guys,I am using SQL 2008 R2, and I do thank all of you guys, REALLY love you.Much thanks for you "Lynn Pettis", your solution is awesome for me and I think that I can amend it to exclude Saturday and SundayBest Regards,HatemThu, 17 May 2012 11:57:27 GMTmota7128http://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxUpdate:[code="sql"]DECLARE @StartDate DATETIME, @EndDate DATETIME;SET @StartDate = '2012-05-18 12:07:00.000'; --GETDATE();SET @EndDate = '2012-05-21 15:40:34.497' ; -- GETDATE() + 1;DECLARE @ElapsedTime INT; -- Elpased Time in munutesSELECT @ElapsedTime = DATEDIFF(n, @StartDate, CASE WHEN DATEDIFF(dd,@StartDate,@EndDate) = 0 THEN @EndDate ELSE DATEADD(n, DATEDIFF(n, 0, CONVERT(DATETIME, '17:00:00', 108)), DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)) END) + -- Calculate elapsed time in minutes for the current date ((DATEDIFF(dd,@StartDate,@EndDate) - 1) * DATEDIFF(n, CONVERT(DATETIME, '08:30:00', 108), CONVERT(DATETIME, '17:00:00', 108))) - -- account for full days between start and end (((DATEDIFF(wk, @StartDate, @EndDate)) * 2) * DATEDIFF(n, CONVERT(DATETIME, '08:30:00', 108), CONVERT(DATETIME, '17:00:00', 108))) + -- reduce time for Saturday and Sunday CASE WHEN DATEDIFF(dd,@StartDate,@EndDate) <> 0 -- if StartDate and EndDate aren't the same day, compute elpased time on last day THEN DATEDIFF(n, DATEADD(n, DATEDIFF(n, 0, CONVERT(DATETIME, '08:30:00', 108)), DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0)), @EndDate) ELSE 0 END; SELECT @ElapsedTime, CAST(@ElapsedTime / 60 AS VARCHAR) + ':' + RIGHT('0' + CAST(@ElapsedTime % 60 AS VARCHAR),2) AS FormatTime;[/code]Thu, 17 May 2012 11:38:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxNeed to verify that you are using SQL Server 2000, as this will determine how to approach this.Wed, 16 May 2012 15:32:05 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspx[quote][b]Lynn Pettis (5/16/2012)[/b][hr]Here is my solution. Give it a test. Oh, with the values given by the OP, I get 12:03 not 12:07.[code="sql"]DECLARE @StartDate DATETIME, @EndDate DATETIME;SET @StartDate = '2012-05-14 12:07:00.000';SET @EndDate = '2012-05-15 15:40:34.497';DECLARE @ElapsedTime INT; -- Elpased Time in munutesSELECT @ElapsedTime = DATEDIFF(n, @StartDate, CASE WHEN DATEDIFF(dd,@StartDate,@EndDate) = 0 THEN @EndDate ELSE DATEADD(n, DATEDIFF(n, 0, CONVERT(DATETIME, '17:00:00', 108)), DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)) END) + ((DATEDIFF(dd,@StartDate,@EndDate) - 1) * DATEDIFF(n, CONVERT(DATETIME, '08:30:00', 108), CONVERT(DATETIME, '17:00:00', 108))) + CASE WHEN DATEDIFF(dd,@StartDate,@EndDate) <> 0 THEN DATEDIFF(n, DATEADD(n, DATEDIFF(n, 0, CONVERT(DATETIME, '08:30:00', 108)), DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0)), @EndDate) ELSE 0 END;SELECT @ElapsedTime, CAST(@ElapsedTime / 60 AS VARCHAR) + ':' + RIGHT('0' + CAST(@ElapsedTime % 60 AS VARCHAR),2) AS FormatTime;[/code][/quote]Just realized this won't work as is over weekend. I will have to work on it to exclude those. It also won't take into account holidays. If this is necessay, you will want to have a calendar table tht indicates what days are work days and incorporate it into the query.Wed, 16 May 2012 15:30:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxHere is my solution. Give it a test. Oh, with the values given by the OP, I get 12:03 not 12:07.[code="sql"]DECLARE @StartDate DATETIME, @EndDate DATETIME;SET @StartDate = '2012-05-14 12:07:00.000';SET @EndDate = '2012-05-15 15:40:34.497';DECLARE @ElapsedTime INT; -- Elpased Time in munutesSELECT @ElapsedTime = DATEDIFF(n, @StartDate, CASE WHEN DATEDIFF(dd,@StartDate,@EndDate) = 0 THEN @EndDate ELSE DATEADD(n, DATEDIFF(n, 0, CONVERT(DATETIME, '17:00:00', 108)), DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)) END) + ((DATEDIFF(dd,@StartDate,@EndDate) - 1) * DATEDIFF(n, CONVERT(DATETIME, '08:30:00', 108), CONVERT(DATETIME, '17:00:00', 108))) + CASE WHEN DATEDIFF(dd,@StartDate,@EndDate) <> 0 THEN DATEDIFF(n, DATEADD(n, DATEDIFF(n, 0, CONVERT(DATETIME, '08:30:00', 108)), DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0)), @EndDate) ELSE 0 END;SELECT @ElapsedTime, CAST(@ElapsedTime / 60 AS VARCHAR) + ':' + RIGHT('0' + CAST(@ElapsedTime % 60 AS VARCHAR),2) AS FormatTime;[/code]Wed, 16 May 2012 12:26:20 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxObs - How does your code account for the start end end of work day requirements? I'm showing results of 27:33 instead of 12:03 (discounting non-working hours)I had a d'uh moment when I saw Lynn's post. Wish I'd remembered to use the modulo operator the first time around. :)Wed, 16 May 2012 12:01:58 GMTAndre Ranierihttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxStart to finish, with stealing a line from Lynn.DECLARE @startdate DATETIME SET @startdate = '2012-05-14 12:07:00.000'Declare @enddate DATETIME SET @enddate= '2012-05-15 15:40:34.497'DECLARE @elapsedtime INT SET @elapsedtime = datediff(mi, @startdate, @enddate)SELECT CAST(@ElapsedTime / 60 AS VARCHAR) + ':' + RIGHT('0' + CAST(@ElapsedTime % 60 AS VARCHAR),2) AS FormatTime;You can always do math to sort out how many minutes you need to subtract.Wed, 16 May 2012 11:56:07 GMTObshttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxOnce you have the elpased time in minutes, the following will format it to HH:MM. This code assumes a positive elpased time. If the computation can go backwards, let me know and I will modify the code appropriately.[code="sql"]DECLARE @ElapsedTime INT; -- Elpased Time in munutesSET @ElapsedTime = 358; -- Test valueSELECT CAST(@ElapsedTime / 60 AS VARCHAR) + ':' + RIGHT('0' + CAST(@ElapsedTime % 60 AS VARCHAR),2) AS FormatTime;[/code]Wed, 16 May 2012 11:32:48 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxThis is some quick and dirty T-SQL that might help you. I'm adding up the total elapsed minutes between the two dates, then run a WHILE loop to increment the hours by 1, and decrement the minutes by 60, as long as the total minutes is greater than 60. I've added some presumptive code in case the two dates aren't consecutive in production. It will add 480 minutes (presumed 8 hr work day) to each day between the two dates, excluding Saturdays and Sundays.If anyone else out there has suggestions for a better way of doing this, I'm always open to constructive feedback.I hope this helps,Andre-- Andre Ranieri 5/16/2012DECLARE @Date1 datetime, @Date2 datetime -- INPUT PARAMETERSSET @Date1 = '2012-05-14 12:07:00.000'SET @Date2 = '2012-05-15 15:40:34.497'DECLARE @TotMin int, @TotHrs INT -- TOTAL ELAPSED MINUTESSET @TotMin = 0SET @TotHrs = 0DECLARE @FirstDateEnd DATETIME, @LastDateBegin DATETIME -- CALCULATED DATE FOR START AND END OF WORKING DAYSET @FirstDateEnd = CAST(@Date1 AS DATE) -- Cast AS Date = Get Midnight TimeSET @FirstDateEnd = DATEADD (hh, 17, @FirstDateEnd) -- 17 hrs from midnight = 5 pmSET @LastDateBegin = CAST(@Date2 AS DATE) -- Cast AS Date = Get Midnight TimeSET @LastDateBegin = DATEADD (mi, 510, @LastDateBegin) -- 510 minutes from midnight = 8:30 AMSET @TotMin = (select datediff (mi, @date1, @FirstDateEnd) )SET @TotMin = @TotMin + (select datediff (mi, @LastDateBegin, @date2) )-- FOR EACH WORKING DAY BETWEEN @DATE1 AND @DATE2 INCREMENT TOTAL MINUTES BY 480 (PRESUMED 8 HR WORK DAY)WHILE CAST(@Date1 as date) < DATEADD(dd, -1, CAST(@LastDateBegin AS date) )BEGIN IF DATEPART(dw, @Date1 ) NOT IN (1,7) -- EXCLUDE WEEKENDS (PRESUMED) BEGIN SET @TotMin = @TotMin + 480 END SET @Date1 = DATEADD(dd, 1, @Date1)END-- Full credit to Lynn Here :)SELECT CAST(@TotMin / 60 AS VARCHAR) + 'hr ' + RIGHT('0' + CAST(@TotMin % 60 AS VARCHAR),2) + 'min' AS OutputWed, 16 May 2012 11:26:13 GMTAndre Ranierihttp://www.sqlservercentral.com/Forums/Topic1301182-110-1.aspxHi All,can you please help me to get the difference between 2 dates, I need the result to be in hour and minutes onlyI tried DateDiff() function and datepart() and everything I know but didn't give me the needed dataFor Examplefirst date (Monday 2012-05-14 12:07:00.000)End Date (Tuesday 2012-05-15 15:40:34.497)working hours starts at 8:30 am and ends at 5pmso the difference should be 4hr:53min + 7hr:10min = 12hr:07minPlease guys help me.Thanks,HatemWed, 16 May 2012 10:02:49 GMTmota7128