Hi All,

Currently my script is using the below mentioned query to find the time difference.

DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))

If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?

Declare @starttime datetime, @endtime datetime, @seconds int

Set @starttime ='2015-10-01 15:05:17'
Set @endtime = '2015-10-01 16:10:16'

set @seconds = DateDiff(second, @starttime, @endtime)
Select Convert(varchar(10), @seconds/3600) + 'hr:'
       +Convert(varchar(10), (@seconds % 3600)/60) + 'mnts:'

Hi 

You could do this

DECLARE @startTime DATETIME = '2015-07-10 10:15:32.050'
DECLARE @EndTime DATETIME = '2015-07-10 12:27:32.050'

SELECT DATEDIFF(HH,@startTime,@EndTime) [Hours],(DATEDIFF(mi,@startTime,@EndTime)%60) [Minutes]

This will divide the time down into the two different components.

• Marked as answer by Balamurugan Ganesan 20 hours 7 minutes ago
• Unmarked as answer by Balamurugan Ganesan 18 hours 40 minutes ago

For this case, I think this is the simplest:

dateadd(ss, hcerrarr - hcreacion, convert(time(0), '00:00'))

That is, first compute the differences between the start and end time and add that in seconds to a time value of midnight.

In general, keep mind that datediff computes boundary passages, so these two return the same:

datediff(HOUR, '12:00:01', '13:59:59')
datediff(HOUR, '12:59:59', '13:00:01')

Hi. I am getting the output. But I am unable to insert the output because the destination column is integer. Please find below the query which I used.

(TOOL_RUN_HRS/3600) + '.' + ((TOOL_RUN_HRS % 3600)/60)

HI Michael, If the time difference is  1 Hr 5 Minutes, I need the output as 1.5. My destination column is integer. I dont want it as two separate columns.

Hi Erland. I am not sure how did you change the query DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion)) as dateadd(ss, hcerrarr - hcreacion, convert(time(0), '00:00')). Could you please elaborate it. Actually I am using three columns named hcreacion, fcreacion and hcerrar.

Hi

You wont be able to store 1.5 into in integer field, it would get rounded off to 1 you might want to store it as a decimal number

Also should 1 hour and 30 min not be rounded off to 1.5? In which case the following would work for you.

DECLARE @startTime DATETIME = '2015-07-10 10:15:00.000'
DECLARE @EndTime DATETIME = '2015-07-10 11:45:00.000'

SELECT DATEDIFF(mi,@startTime,@EndTime)/60.0

If you are sure that that is the format you want then you can just concatenate the fields

DECLARE @startTime DATETIME = '2015-07-10 10:15:00.000'
DECLARE @EndTime DATETIME = '2015-07-10 11:20:00.000'

SELECT cast(DATEDIFF(HH,@startTime,@EndTime) as varchar(10)) +'.'+cast((DATEDIFF(mi,@startTime,@EndTime)%60) as varchar(2))

Hi Michael. The filed type is Numeric(7,2). Let me check your query.

hope below code help you

declare @startDate as datetime
set @startDate = getdate()
declare @enddate as datetime
set @enddate =(select dateadd(mi,90,getdate()))

select cast(datediff(mi,@startdate, @endDate)/60 as varchar(10)) + '.' + cast(datediff(mi,@startdate, @endDate)%60 as varchar(10))

Thanks Michael and Tejinder. I am getting the output. Only concern is 1 hr 5 minutes and 1 hr 50 minutes are having the same value as 1.50. Is it possible to make 1 hr 5 minutes as 1.05 to differentiate from 1 hr 50  minutes?

DECLARE @startTime DATETIME = '2015-07-10 00:00:01', @endTime DATETIME = '2015-07-10 00:00:02'

SELECT RIGHT('00' +  CAST(((DATEDIFF(SECOND,@startTime,@endTime)/ 3600%24)) AS VARCHAR),2)+':'+
       RIGHT('00' +  CAST((DATEDIFF(SECOND,@startTime,@endTime) % 3600) / 60 AS VARCHAR),2)+':'+
	   RIGHT('00' +  CAST(DATEDIFF(SECOND,@startTime,@endTime) % 60 AS VARCHAR),2)

Thanks a lot Patrick. I got the result as expected.

Hi Erland. I am not sure how did you change the query DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion)) as dateadd(ss, hcerrarr - hcreacion, convert(time(0), '00:00')). Could you please elaborate it. Actually I am using three columns named hcreacion, fcreacion and hcerrar.

Simple mathematics. You have, peeling off all that dateadd/datediff stuff.

  fcreacion + hcerrar - (fcreacion + hcreaction) =

  fcreacion + hcerrar - fcreacion - hcreaction) =

  hcerrar - hcreaction

Then I decided to converted data to the time data type, because that makes sense.

On the other hand, it makes little sense to store 1.5 in a numeric(7,2) column and have to mean 1hr50minutes. If you think that is correct, I recommend that you review the requirements again. In a numeric(7,2) I would expect 1:50 to be stored as 1.83.