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.
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 secondsSELECT 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 AthalyeIndia."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 methodselect Elapsed_Minutes = datediff(mi,0,ET-ST), Elapsed_Time = ET-ST, Start_Time = ST, End_time = ETfrom ( 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-STHow 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 LarssonHelsingborg, Sweden |
|
|
ashishmgupta
Starting Member
12 Posts |
Posted - 2006-11-11 : 05:58:54
|
Thank you people for the help! :) |
|
|
SuSaya
Starting Member
1 Post |
Posted - 2013-05-29 : 05:08:03
|
select Convert(time(0),(Convert(datetime,'05:48:10.420') - Convert(datetime,'05:47:53.497')),8) |
|
|
|
|
|
|
|