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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Calculating the time difference Reply to Topic  Printer Friendly
Author  Topic

ashishmgupta
Starting Member

12 Posts

 Posted - 11/10/2006 :  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
Flowing Fount of Yak Knowledge

India
5581 Posts

 Posted - 11/10/2006 :  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" Edited by - harsh_athalye on 11/10/2006 07:18:49

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/10/2006 :  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)

USA
7020 Posts

 Posted - 11/10/2006 :  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

United Kingdom
22859 Posts

 Posted - 11/10/2006 :  15:24:09 ET-STHow cool is that!

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 11/10/2006 :  15:33:44 I thought it was ET phone home?Peter LarssonHelsingborg, Sweden

ashishmgupta
Starting Member

12 Posts

 Posted - 11/11/2006 :  05:58:54 Thank you people for the help! :)

SuSaya
Starting Member

1 Posts

 Posted - 05/29/2013 :  05:08:03 select Convert(time(0),(Convert(datetime,'05:48:10.420') - Convert(datetime,'05:47:53.497')),8)
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC