Please help me (Date Difference Issue)

  • 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

  • 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

  • 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;

  • 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.

  • 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.

    🙂

  • 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;

  • 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.

  • Need to verify that you are using SQL Server 2000, as this will determine how to approach this.

  • 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;

  • 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

  • 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.

  • 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