Calculating the time difference

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating the time difference

Author

Topic

ashishmgupta
Starting Member

12 Posts

Posted - 2006-11-10 : 07:11:20

We have this StartTime and EndTime (both datetime fields) and now we are calculating the difference in minutes beween two dates.Using datediff(mi,StartTime,EndTime) giving iccorect result e.g:-

SELECT datediff(mi,'2006-11-10 05:47:53.497','2006-11-10 05:48:10.420') gives 1 minute where as it is 17 seconds.What is the best way to calculate the difference in minutes?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 07:16:10

That is because you are asking SQL to return difference in minutes...Use ss or s or second to return in seconds

SELECT datediff(ss,'2006-11-10 05:47:53.497','2006-11-10 05:48:10.420')

Or if you want minutes in fractions:

SELECT convert(decimal(5,2),datediff(ss,'2006-11-10 05:47:53.497','2006-11-10 05:48:10.498')/60.00)

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-11-10 : 07:26:27

DATEDIFF is showing the date difference on the Minutes value of the two date/times. It isn't interested in the number of elapsed minutes BETWEEN the two dates (dunno why, but that's how it is!)

Consider these two datetimes. Both are 200 ms apart, but the first one spans a minute boundary, the second doesn't.

select DATEDIFF(Minute, '2006-11-10 00:00:59.900', '2006-11-10 00:01:00.100')
select DATEDIFF(Second, '2006-11-10 00:00:59.900', '2006-11-10 00:01:00.100')
select DATEDIFF(Millisecond, '2006-11-10 00:00:59.900', '2006-11-10 00:01:00.100')

select DATEDIFF(Minute, '2006-11-10 00:00:00.100', '2006-11-10 00:00:00.300')
select DATEDIFF(Second, '2006-11-10 00:00:00.100', '2006-11-10 00:00:00.300')
select DATEDIFF(Millisecond, '2006-11-10 00:00:00.100', '2006-11-10 00:00:00.300')

As Harsh said, you need to evaluate in seconds and divide by 60 to get the "elapsed minutes" - and if you are rounding the result you may actually need to calculate it in milliseconds!

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-10 : 08:34:15

Another method


select
	Elapsed_Minutes = datediff(mi,0,ET-ST),
	Elapsed_Time = ET-ST,
	Start_Time = ST,
	End_time = ET
from
	(
	select
		ST=convert(datetime,'2006-11-10 05:47:53.497'),
		ET=convert(datetime,'2006-11-10 05:48:10.420')
	) a

Results:


Elapsed_Minutes Elapsed_Time            Start_Time              End_time
--------------- ----------------------- ----------------------- -----------------------
0               1900-01-01 00:00:16.923 2006-11-10 05:47:53.497 2006-11-10 05:48:10.420

(1 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-11-10 : 15:24:09

ET-ST

How cool is that!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 15:33:44

I thought it was ET phone home?

Peter Larsson
Helsingborg, Sweden

ashishmgupta
Starting Member

12 Posts

Posted - 2006-11-11 : 05:58:54