September 6, 2013 at 12:24 pm
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
September 6, 2013 at 12:48 pm
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
September 6, 2013 at 12:49 pm
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');
September 6, 2013 at 12:53 pm
Thanks
September 6, 2013 at 1:02 pm
Is there a way to mark a question as answered in SQL Server Central?
September 6, 2013 at 1:04 pm
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.
September 6, 2013 at 1:23 pm
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
September 6, 2013 at 1:49 pm
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.
September 6, 2013 at 3:49 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply