Calculating Time Difference 1

[RJL1]

Hello folks

I been wracking my brain trying to figure out how to calculate time in minutes and seconds. I have these 2 date time values

DECLARE              @STARTDATE    DATETIME
DECLARE              @ENDDATE      DATETIME 

SET         @STARTDATE		= '2/7/2013 9:51:54'
SET         @ENDDATE        = '2/7/2013 9:56:41'

SELECT 
'Elapsed Minutes' = DATEDIFF(mi,@ENDDATE,@STARTDATE),
'Elapsed Time' = @ENDDATE - @STARTDATE,
'Start Time' = @STARTDATE,
'End Time' = @ENDDATE

My elapsed ('Elapsed Time' = @ENDDATE - @STARTDATE) time comes back like this 1900-01-01 00:04:47.000 which is the correct value for the time 4 minutes 47 seconds. I need to get just the time value from this.

What is the best way to calculate the time between two date time fields and get just the time portion.

So for this example I need to get 4:47 and no date and no milliseconds in decimal format

Thanks in advance
RJL

 

[drlex]

Slightly confused on 'decimal format', as 4m47s = 4.78 mins
Assuming that you want either 4:47 or 04:47, and that you don't have SS2008 (TIME extraction), how about:

'Elapsed Time mm:ss' = RIGHT(CONVERT(VARCHAR(8),@ENDDATE - @STARTDATE,108),5)

or 

'Elapsed Time  m:ss' = CAST(datepart(mi,@ENDDATE - @STARTDATE)as varchar(2)) + ':' + cast(datepart(ss,@ENDDATE - @STARTDATE) as varchar(2))

soi là, soi carré

 

[RJL1]

Brilliant, you were right I needed 4:47 but usinf DATEDIFF I was getting 4.78. Work like a champ

Thanks
RJL