Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Please help me (Date Difference Issue) Expand / Collapse
Author
Message
Posted Wednesday, May 16, 2012 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:35 AM
Points: 4, Visits: 22
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
Post #1301182
Posted Wednesday, May 16, 2012 11:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:28 PM
Points: 287, Visits: 305
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

Post #1301240
Posted Wednesday, May 16, 2012 11:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 23,396, Visits: 32,232
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1301245
Posted Wednesday, May 16, 2012 11:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 6:59 AM
Points: 247, Visits: 420
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.



Post #1301262
Posted Wednesday, May 16, 2012 12:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:28 PM
Points: 287, Visits: 305
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.

:)
Post #1301264
Posted Wednesday, May 16, 2012 12:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 23,396, Visits: 32,232
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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1301280
Posted Wednesday, May 16, 2012 3:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 23,396, Visits: 32,232
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1301398
Posted Wednesday, May 16, 2012 3:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 23,396, Visits: 32,232
Need to verify that you are using SQL Server 2000, as this will determine how to approach this.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1301400
Posted Thursday, May 17, 2012 11:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 23,396, Visits: 32,232
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1302034
Posted Thursday, May 17, 2012 11:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:35 AM
Points: 4, Visits: 22
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
Post #1302057
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse