Solved

datediff format

Posted on 2000-03-30
2
382 Views
Last Modified: 2006-11-17
I want to return the difference between two dates in hours and minutes. I am using the following code to return hours:

datediff(hh,pd.starttime,pd.endtime) duration

can anyone tell me how to get minutes as well.

Thanks
0
Comment
Question by:cb1
2 Comments
 
LVL 1

Expert Comment

by:drom
ID: 2669380
short answer:
DATEDIFF(mi,pd.starttime,pd.endtime)

a bit larger:
select STR(DATEDIFF(hh,pd.starttime,pd.endtime)) + ":" + STR(DATEDIFF(mi,pd.starttime,pd.endtime) - DATEDIFF(hh,pd.starttime,pd.endtime)*60, 2)
from tablename pd

will give you time difference in string in hhh:mm format (one dowside - it's space instead of leading '0' in mm)

Regards
Dmitry.
0
 
LVL 4

Accepted Solution

by:
Gustavo Perez Buenrostro earned 100 total points
ID: 2670912
cb1,
Next code lets you retrieve the result you want (using MS-SQL Time Format):

declare @dtStartTime datetime
       ,@dtEndTime datetime

select @dtStartTime='20000301 15:23:55.157'
      ,@dtEndTime='20000301 16:24:56.158'

select left(convert(varchar,dateadd(ms,datediff (ms,@dtStartTime,@dtEndTime),0),108),5)



Here is the result:

-----
01:01
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now