Datdiff of hh,mm.mi

Question

Post reply

Datdiff of hh,mm.mi

learnsql84

SSC Rookie

Points: 27
More actions
November 14, 2011 at 1:50 pm

#390856

Hello All,
Can someone please help me with a sql query I am truing to find the diference of two datetime in hh(hour):M(minutes):ss(seconds) format.
ex: if Startdate is "2011-11-11 15:25:26.680" and end date is "2011-11-11 15:25:48.957". I need to find the difference of time say '00hr:00min:22sec' somethiing like this.
please help me..
thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

something like this?

select [Years] = datediff(year,0,ET-ST)-1,

[Months] = datepart(month,ET-ST)-1,

[Days] = datepart(day,ET-ST)-1,

[Hours] = datepart(Hour,ET-ST),

[Minutes] = datepart(Minute,ET-ST),

[Seconds] = datepart(Second,ET-ST),

[Milliseconds] = datepart(millisecond,ET-ST)

from

(

select -- Test Data

ST = convert(datetime,'2008/09/22 00:35:33.997'),

ET = convert(datetime,'2009/10/23 04:05:45.443')

) a

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Use DATEDIFF in seconds, then do some math. Note the use of integers for the division to keep it in whole numbers.

IE:

DECLARE @DT1 DATETIME,

@dt2 DATETIME

SELECT@DT1 = '20100101 10:30:45',

@dt2 = '20100101 12:28:39'

SELECT

DATEDIFF( ss, @DT1, @dt2) AS TotalSeconds,

DATEDIFF( ss, @DT1, @dt2)/ 60 AS TotalMinutes,

DATEDIFF( ss, @DT1, @dt2)/60/60 AS TotalHours,

DATEDIFF( ss, @DT1, @dt2)/60%60 AS LeftoverMinutes,

DATEDIFF( ss, @DT1, @dt2)%60 AS LeftoverSeconds,

CONVERT( VARCHAR(3), DATEDIFF( ss, @DT1, @dt2)/60/60) + 'hh:' +

RIGHT( '00' + CONVERT( VARCHAR(2), DATEDIFF( ss, @DT1, @dt2)/60%60), 2) + 'mm:' +

RIGHT( '00' + CONVERT( VARCHAR(2), DATEDIFF( ss, @DT1, @dt2)%60), 2) + 'ss'

EDIT: Sniped by Lowell. Hm, nice code there. Like the datepart trick.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

learnsql84 SSC Rookie Points: 27 More actions · Answer 3

Thanks for the reply guys

I got a simple way out for it

SELECT convert(varchar, EndDateTime- StartDateTime, 114) FROM Yourtablename

O/p will be :00:00:22:277

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

learnsql84 (11/14/2011)
Thanks for the reply guys
I got a simple way out for it
SELECT convert(varchar, EndDateTime- StartDateTime, 114) FROM Yourtablename
O/p will be :00:00:22:277

It works, but it won't catch more than 24 hours correctly. Look at the results of this:

DECLARE @DT1 DATETIME,

@dt2 DATETIME

SELECT@DT1 = '20100101 10:30:45',

@dt2 = '20100103 12:28:39'

SELECT convert(varchar, @dt2 - @DT1, 114)

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Lowell (11/14/2011)
something like this?
select [Years] = datediff(year,0,ET-ST)-1,
[Months] = datepart(month,ET-ST)-1,
[Days] = datepart(day,ET-ST)-1,
[Hours] = datepart(Hour,ET-ST),
[Minutes] = datepart(Minute,ET-ST),
[Seconds] = datepart(Second,ET-ST),
[Milliseconds] = datepart(millisecond,ET-ST)
from
(
select -- Test Data
ST = convert(datetime,'2008/09/22 00:35:33.997'),
ET = convert(datetime,'2009/10/23 04:05:45.443')
) a

I was under the impression that using '-' or '+' on the new datetime data types will no longer work. So, I tested the above and changed the data type to datetime2 and it did indeed fail.

I would caution using the above if you will be moving to the new date/time data types.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

diamondgm SSCrazy Points: 2824 More actions · Answer 6

Another way of doing it if you never have spans exceeding 24hr:

SELECT CAST(DATEADD(MILLISECOND, (DATEDIFF(MILLISECOND, '2011-11-11 15:25:26.680', '2011-11-11 15:25:48.957')),CAST(0 AS DATETIME) ) AS TIME)

There are probably simpler ways to do this 😀

learnsql84 SSC Rookie Points: 27 More actions · Answer 7

learnsql84

SSC Rookie

Points: 27

November 15, 2011 at 12:04 pm

#1408109

Thanks You all guys...U all are great help...

p.ramchander Ten Centuries Points: 1171 More actions · Answer 8

DECLARE @STARTDATE DateTime

SET @STARTDATE = '2011/11/11 15:25:26.680'

DECLARE @ENDDATE DateTime

SET @ENDDATE = '2011/11/11 15:25:48.957'

SELECT(DATEDIFF(d,@STARTDATE, @ENDDATE)) as DaysDiff,

(DATEDIFF(ww,@STARTDATE, @ENDDATE)) as WeeksDiff,

(DATEDIFF(hh,@STARTDATE, @ENDDATE)) as HoursDiff,

(DATEDIFF(MINUTE,@STARTDATE, @ENDDATE)) as MinDiff,

(DATEDIFF(SS,@STARTDATE, @ENDDATE)) AS SecDiff,

CAST(DATEDIFF(HH,@STARTDATE,@ENDDATE) AS VARCHAR) + ':' + CAST(DATEDIFF(MINUTE,@STARTDATE, @ENDDATE)%60 AS VARCHAR)