DateDiff to display hours and minutes

Question

my code gives TOTAL HOURS in hours, but I'm trying to print something like

TotalHours 8:36 

where 8 represents the hourly part, and 36 represents the minute average totalHours of people worked for one day in the office.

 with times as ( SELECT t1.EmplID , t3.EmplName , min(t1.RecTime) AS InTime , max(t2.RecTime) AS [TimeOut] , t1.RecDate AS [DateVisited] FROM AtdRecord t1 INNER JOIN AtdRecord t2 ON t1.EmplID = t2.EmplID AND t1.RecDate = t2.RecDate AND t1.RecTime < t2.RecTime inner join HrEmployee t3 ON t3.EmplID = t1.EmplID group by t1.EmplID , t3.EmplName , t1.RecDate ) SELECT EmplID , EmplName , InTime , [TimeOut] , [DateVisited] , DATEDIFF(Hour,InTime, [TimeOut]) TotalHours from times Order By EmplID, DateVisited 

Answer 1

A small change like this can be made

  SELECT EmplID , EmplName , InTime , [TimeOut] , [DateVisited] , CASE WHEN minpart=0 THEN CAST(hourpart as nvarchar(200))+':00' ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time' FROM ( SELECT EmplID, EmplName, InTime, [TimeOut], [DateVisited], DATEDIFF(Hour,InTime, [TimeOut]) as hourpart, DATEDIFF(minute,InTime, [TimeOut])%60 as minpart from times) source 

Answer 2

Very simple:

 CONVERT(TIME,Date2 - Date1) 

For example:

 Declare @Date2 DATETIME = '2016-01-01 10:01:10.022' Declare @Date1 DATETIME = '2016-01-01 10:00:00.000' Select CONVERT(TIME,@Date2 - @Date1) as ElapsedTime 

Yelds:

 ElapsedTime ---------------- 00:01:10.0233333 (1 row(s) affected) 

Answer 3

Try this request

 select *, Days = datediff(dd,0,DateDif), Hours = datepart(hour,DateDif), Minutes = datepart(minute,DateDif), Seconds = datepart(second,DateDif), MS = datepart(ms,DateDif) from (select DateDif = EndDate-StartDate, aa.* from ( -- Test Data Select StartDate = convert(datetime,'20090213 02:44:37.923'), EndDate = convert(datetime,'20090715 13:24:45.837')) aa ) a 

Exit

 DateDif StartDate EndDate Days Hours Minutes Seconds MS ----------------------- ----------------------- ----------------------- ---- ----- ------- ------- --- 1900-06-02 10:40:07.913 2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 152 10 40 7 913 (1 row(s) affected) 

Answer 4

I would make your final choice as follows:

 SELECT EmplID , EmplName , InTime , [TimeOut] , [DateVisited] , CONVERT(varchar(3),DATEDIFF(minute,InTime, TimeOut)/60) + ':' + RIGHT('0' + CONVERT(varchar(2),DATEDIFF(minute,InTime,TimeOut)%60),2) as TotalHours from times Order By EmplID, DateVisited 

Any solution trying to use DATEDIFF(hour,... should be complicated (if it is correct), because DATEDIFF counts the transitions - DATEDIFF(hour,...09:59',...10:01') will return 1 due to the transition of hours from 9 to 10. So I'm just using DATEDIFF in minutes.

The foregoing may still be inaccurate if seconds are involved (it may overestimate a bit since its counting minutes pass), so if you need second or millisecond precision, you need to configure DATEDIFF to use these units, and then apply the appropriate constant separation ( according to the clock above) to simply return the hours and minutes.

Answer 5

Just change

 DATEDIFF(Hour,InTime, [TimeOut]) TotalHours 

part

 CONCAT((DATEDIFF(Minute,InTime,[TimeOut])/60),':', (DATEDIFF(Minute,InTime,[TimeOut])%60)) TotalHours 

The / 60 option gives you hours,% 60 gives you the remaining minutes, and CONCAT allows you to set a colon between them.

I know this is an old question, but I stumbled upon it and thought it might help if someone else met him.

Answer 6

Put your appropriate value and try the following:

 declare @x int, @y varchar(200), @dt1 smalldatetime = '2014-01-21 10:00:00', @dt2 smalldatetime = getdate() set @x = datediff (HOUR, @dt1, @dt2) set @y = @x * 60 - DATEDIFF(minute,@dt1, @dt2) set @y = cast(@x as varchar(200)) + ':' + @y Select @y 

Answer 7

it will help you

  DECLARE @DATE1 datetime = '2014-01-22 9:07:58.923' DECLARE @DATE2 datetime = '2014-01-22 10:20:58.923' SELECT DATEDIFF(HOUR, @DATE1,@DATE2) , DATEDIFF(MINUTE, @DATE1,@DATE2) - (DATEDIFF(HOUR,@DATE1,@DATE2)*60) SELECT CAST(DATEDIFF(HOUR, @DATE1,@DATE2) AS nvarchar(200)) + ':'+ CAST(DATEDIFF(MINUTE, @DATE1,@DATE2) - (DATEDIFF(HOUR,@DATE1,@DATE2)*60) AS nvarchar(200)) As TotalHours 

Answer 8

Since any DateTime can be transferred to float, and the decimal part of the number represents the very time:

 DECLARE @date DATETIME = GETDATE() SELECT CAST(CAST(@date AS FLOAT) - FLOOR(CAST(@date AS FLOAT)) AS DATETIME 

This will result in a time date, for example, “1900-01-01 hour of the day”, you can use it as a time, a timestamp, or even use a conversion to get a formatted time.

I assume that this works in any version of SQL, since casting date and time in float is compatible with version 2005.

Hope this helps.

Answer 9

Divide the Datediff in MS by the number of ms per day, add to Datetime , and then to time:

 Declare @D1 datetime = '2015-10-21 14:06:22.780', @D2 datetime = '2015-10-21 14:16:16.893' Select Convert(time,Convert(Datetime, Datediff(ms,@d1, @d2) / 86400000.0)) 

Answer 10

If you want the 08:30 format (HH: MM), try this,

 SELECT EmplID , EmplName , InTime , [TimeOut] , [DateVisited] , RIGHT('0' + CONVERT(varchar(3),DATEDIFF(minute,InTime, TimeOut)/60),2) + ':' + RIGHT('0' + CONVERT(varchar(2),DATEDIFF(minute,InTime,TimeOut)%60),2) as TotalHours from times Order By EmplID, DateVisited 

Answer 11

No need to jump through hoops. Subtracting Start from End essentially gives you time (combining the answers of Vinyesh Kumar and Karl Nietzsche):

 SELECT *, --as a time object TotalHours = CONVERT(time, EndDate - StartDate), --as a formatted string TotalHoursText = CONVERT(varchar(20), EndDate - StartDate, 114) FROM ( --some test values (across days, but OP only cares about the time, not date) SELECT StartDate = CONVERT(datetime,'20090213 02:44:37.923'), EndDate = CONVERT(datetime,'20090715 13:24:45.837') ) t 

Ouput

 StartDate EndDate TotalHours TotalHoursText ----------------------- ----------------------- ---------------- -------------------- 2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 10:40:07.9130000 10:40:07:913 

See the full list of options and convert here: https://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 12

In case someone is still looking for a request to display the difference in the format hr min and sec: (This will display the difference in this format: 2 hr 20 min 22 sec)

 SELECT CAST(DATEDIFF(minute, StartDateTime, EndDateTime)/ 60 as nvarchar(20)) + ' hrs ' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)/60 as nvarchar(20)) + ' mins' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)% 60 as nvarchar(20)) + ' secs' 

OR can be in the format as in the question:

 CAST(DATEDIFF(minute, StartDateTime, EndDateTime)/ 60 as nvarchar(20)) + ':' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)/60 as nvarchar(20))