[sql] DateDiff to output hours and minutes

my code gives TOTAL HOURS in hours, but i am trying to output something like

TotalHours 
  8:36

where 8 represents hour part and 36 represents minutes part mean totalHours a person has worked in a single day at 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 

This question is related to sql sql-server

The answer is


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

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))

Since any DateTime can be cast to a float, and the decimal part of the number represent the time itself:

DECLARE @date DATETIME = GETDATE()

SELECT CAST(CAST(@date AS FLOAT) - FLOOR(CAST(@date AS FLOAT)) AS DATETIME

This will result a datetime like '1900-01-01 hour of the day' you can cast it as time, timestamp or even use convert to get the formatted time.

I guess this works in any version of SQL since cast a datetime to float is compatible since version 2005.

Hope it helps.


Try this query

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

Output

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)

Divide the Datediff in MS by the number of ms in a day, cast 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))

Please put your related value and try this :

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

Just change the

DATEDIFF(Hour,InTime, [TimeOut]) TotalHours

part to

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

The /60 gives you hours, the %60 gives you the remaining minutes, and CONCAT lets you put a colon between them.

I know it's an old question, but I came across it and thought it might help if someone else comes across it.


Very simply:

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)

this would hep 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 

If you want 08:30 ( HH:MM) format then 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

No need to jump through hoops. Subtracting Start from End essentially gives you the timespan (combining Vignesh Kumar's and Carl Nitzsche's answers) :

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 cast and convert options here: https://msdn.microsoft.com/en-us/library/ms187928.aspx


I would make your final select as:

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,... is bound to be complicated (if it's correct) because DATEDIFF counts transitions - DATEDIFF(hour,...09:59',...10:01') will return 1 because of the transition of the hour from 9 to 10. So I'm just using DATEDIFF on minutes.

The above can still be subtly wrong if seconds are involved (it can slightly overcount because its counting minute transitions) so if you need second or millisecond accuracy you need to adjust the DATEDIFF to use those units and then apply suitable division constants (as per the hours one above) to just return hours and minutes.