Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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