May 16, 2012 at 10:02 am
Hi All,
can you please help me to get the difference between 2 dates, I need the result to be in hour and minutes only
I tried DateDiff() function and datepart() and everything I know but didn't give me the needed data
For Example
first 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 5pm
so the difference should be 4hr:53min + 7hr:10min = 12hr:07min
Please guys help me.
Thanks,
Hatem
May 16, 2012 at 11:26 am
This 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/2012
DECLARE @Date1 datetime, @Date2 datetime -- INPUT PARAMETERS
SET @Date1 = '2012-05-14 12:07:00.000'
SET @Date2 = '2012-05-15 15:40:34.497'
DECLARE @TotMin int, @TotHrs INT -- TOTAL ELAPSED MINUTES
SET @TotMin = 0
SET @TotHrs = 0
DECLARE @FirstDateEnd DATETIME, @LastDateBegin DATETIME -- CALCULATED DATE FOR START AND END OF WORKING DAY
SET @FirstDateEnd = CAST(@Date1 AS DATE)-- Cast AS Date = Get Midnight Time
SET @FirstDateEnd = DATEADD (hh, 17, @FirstDateEnd)-- 17 hrs from midnight = 5 pm
SET @LastDateBegin = CAST(@Date2 AS DATE)-- Cast AS Date = Get Midnight Time
SET @LastDateBegin = DATEADD (mi, 510, @LastDateBegin)-- 510 minutes from midnight = 8:30 AM
SET @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 Output
May 16, 2012 at 11:32 am
Once 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.
DECLARE @ElapsedTime INT; -- Elpased Time in munutes
SET @ElapsedTime = 358; -- Test value
SELECT CAST(@ElapsedTime / 60 AS VARCHAR) + ':' + RIGHT('0' + CAST(@ElapsedTime % 60 AS VARCHAR),2) AS FormatTime;
May 16, 2012 at 11:56 am
Start 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.
May 16, 2012 at 12:01 pm
Obs - 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.
🙂
May 16, 2012 at 12:26 pm
Here is my solution. Give it a test. Oh, with the values given by the OP, I get 12:03 not 12:07.
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 munutes
SELECT @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;
May 16, 2012 at 3:30 pm
Lynn Pettis (5/16/2012)
Here is my solution. Give it a test. Oh, with the values given by the OP, I get 12:03 not 12:07.
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 munutes
SELECT @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;
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.
May 16, 2012 at 3:32 pm
Need to verify that you are using SQL Server 2000, as this will determine how to approach this.
May 17, 2012 at 11:38 am
Update:
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 munutes
SELECT @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;
May 17, 2012 at 11:57 am
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 Sunday
Best Regards,
Hatem
May 17, 2012 at 12:05 pm
mota7128 (5/17/2012)
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 Sunday
Best Regards,
Hatem
Look close, I did that already (hopefully) in the code I just posted. Be sure to test it in your environment.
May 17, 2012 at 3:03 pm
Sorry, 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.
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
Always remember to test.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply