Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

datediff format

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
cb1
Asked:
cb1
1 Solution
 
dromCommented:
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
 
Gustavo Perez BuenrostroCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now