DateDiff to output hours and minutes

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] DateDiff to output hours and minutes

Examples related to sql

Examples related to sql-server