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

Time difference Expand / Collapse
Author
Message
Posted Friday, September 6, 2013 12:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
Hi all,

I have a rather basic question, but having difficulty.... I need to calculate the difference between 2 times. Any help would be appreciated.

Example:

09:28:00 - 09:38:00 = 00:10:00
Post #1492362
Posted Friday, September 6, 2013 12:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:17 AM
Points: 1,184, Visits: 1,220
Take a look at the DATEDIFF() function: http://technet.microsoft.com/en-us/library/ms189794(v=sql.105).aspx

DATEDIFF ( datepart , startdate , enddate )

HTH,
Rob
Post #1492373
Posted Friday, September 6, 2013 12:49 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 3,983, Visits: 3,421
DaveDB (9/6/2013)
Hi all,

I have a rather basic question, but having difficulty.... I need to calculate the difference between 2 times. Any help would be appreciated.

Example:

09:28:00 - 09:38:00 = 00:10:00

You can use the DATEDIFF built-in function.

select datediff(minute, '09:28:00', '09:38:00');




Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1492376
Posted Friday, September 6, 2013 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
Thanks
Post #1492378
Posted Friday, September 6, 2013 1:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
Is there a way to mark a question as answered in SQL Server Central?
Post #1492380
Posted Friday, September 6, 2013 1:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 3,983, Visits: 3,421
DaveDB (9/6/2013)
Is there a way to mark a question as answered in SQL Server Central?

Not that I've seen. Most volunteers open a question, read it, see that it's been answered and simply move on to the next one. The question eventually falls off the first page or two, where it won't be seen again until someone looks for it.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1492383
Posted Friday, September 6, 2013 1:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
This isn't working. Please tell me what I am missing...

cast(DATEDIFF(hh, segstart, segstop)+':'+DATEDIFF(mm, segstart, segstop)+':'+DATEDIFF(ss, segstart, segstop)as nvarchar(25)) as a

Post #1492390
Posted Friday, September 6, 2013 1:49 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 3,983, Visits: 3,421
If you're sure that all you need is HH:MM:SS, then this will work.

with cte as (
select datediff(second, '09:28:00', '13:38:02') diff)
select
right('00' + convert(varchar(2), (diff % 86400) / 3600), 2) + ':'+
right('00' + convert(varchar(2), (diff % 3600) / 60), 2) + ':' +
right('00' + convert(varchar(2), (diff % 60)), 2)
from cte;

If you want to calculate months, days, hours, minutes and seconds, then we'll need a different approach.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1492403
Posted Friday, September 6, 2013 3:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:04 PM
Points: 2,102, Visits: 3,164
For anything less than 24 hours, you can do this:

SELECT
CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, segstart, segstop), 0), 8)
FROM (
SELECT CAST('20130906 09:28:00' AS datetime) AS segstart, CAST('20130906 09:37:22' AS datetime) AS segstop
) AS test_data


Edit: Added test data.


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1492446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse