Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DateDiff to output hours and minutes

Tags:

sql

sql-server

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 
like image 592
Evil rising Avatar asked Jan 21 '14 05:01

Evil rising


People also ask

How do I calculate hours and minutes between two dates in SQL?

To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year .

How do I calculate hours between two dates in SQL?

SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.

What is the output of datediff?

The DATEDIFF() function returns a value of integer indicating the difference between the start_date and end_date , with the unit specified by date_part . The DATEDIFF() function returns an error if the result is out of range for integer (-2,147,483,648 to +2,147,483,647).

How do I calculate difference in minutes in SQL?

MySQL TIMEDIFF() Function The TIMEDIFF() function returns the difference between two time/datetime expressions. Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.


9 Answers

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)
like image 175
MaurGi Avatar answered Oct 02 '22 18:10

MaurGi


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)
like image 30
Vignesh Kumar A Avatar answered Oct 02 '22 18:10

Vignesh Kumar A


Small change like this can be done

  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
like image 41
DhruvJoshi Avatar answered Oct 02 '22 18:10

DhruvJoshi


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.

like image 31
Damien_The_Unbeliever Avatar answered Oct 02 '22 16:10

Damien_The_Unbeliever


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.

like image 44
Rhino Avatar answered Oct 02 '22 18:10

Rhino


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))
like image 36
Carl Nitzsche Avatar answered Oct 02 '22 16:10

Carl Nitzsche


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
like image 23
MAX Avatar answered Oct 02 '22 18:10

MAX


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
like image 44
Faraz Ahmed Avatar answered Oct 02 '22 18:10

Faraz Ahmed


Difference Two Time in [hh:mm:ss]

select FORMAT((CONVERT(datetime,'2021-12-01 19:24:40') - CONVERT(datetime,'2021-12-01 17:00:00')),'hh:mm:ss')DffTime
like image 24
Mahydul Islam Shajal Avatar answered Oct 02 '22 18:10

Mahydul Islam Shajal