SQL time difference between two dates result in hh mm ss

Question

I am facing some difficulty with calculating the time difference between two dates   What I want is  I have two dates let say    StartDate    10 01 2012 08 40 18 000   EndDate  10 04 2012 09 52 48 000    so the difference between two dates in the form of hh mm ss is 72 42 30    How can I get this result in a T-SQL query

User · Answer

Take a look at these  I didn t use more parenthesis to keep it readable  so remember that multiplication is done before addition or subtraction   Both below return   hr  mins  sec   timediff 73   12    30   73 12 30   This is written to not use a sub-query and be the most readable and understandable     declare  StartDate datetime   EndDate datetime  set  StartDate    10 01 2012 08 40 18 000  set  EndDate      10 04 2012 09 52 48 000    select datediff hour   StartDate   EndDate  hr     datediff minute   StartDate   EndDate      - datediff hour   StartDate   EndDate    60 mins     datediff second   StartDate   EndDate      -  datediff minute   StartDate   EndDate    60  sec     cast datediff hour   StartDate   EndDate  as varchar           cast datediff minute   StartDate   EndDate      - datediff hour   StartDate   EndDate    60 as varchar           cast datediff second   StartDate   EndDate      -  datediff minute   StartDate   EndDate    60  as varchar  timediff   This is a version that would perform better if you have a lot of data  It requires a sub-query   declare  StartDate datetime   EndDate datetime  set  StartDate    10 01 2012 08 40 18 000  set  EndDate      10 04 2012 09 52 48 000    select s seconds   3600  hrs  s seconds   60 -  seconds   3600     60 mins  s seconds -  s seconds   60    60   seconds  cast s seconds   3600 as varchar          cast  s seconds   60 -  seconds   3600     60  as varchar          cast  s seconds -  s seconds   60    60  as varchar  timediff from  select datediff second   StartDate   EndDate  as seconds  s

User · Answer

declare  StartDate datetime   EndDate datetime  set  StartDate    10 01 2012 08 40 18 000  set  EndDate      10 04 2012 09 52 48 000   SELECT CONVERT CHAR 8   CAST CONVERT varchar 23   EndDate 121  AS DATETIME  -CAST CONVERT varchar 23   StartDate 121 AS DATETIME  8  AS TimeDiff

User · Answer

I came across this post today as I was trying to gather the time difference between fields located in separate tables joined together on a key field  This is the working code for such an endeavor    tested in sql 2010  Bare in mind that my original query co-joined 6 tables on a common keyfield  in the code below I have removed the other tables as to not cause any confusion for the reader   The purpose of the query is to calculate the difference between the variables CreatedUTC  amp  BackupUTC  where difference is expressed in days and the field is called  DaysActive    declare  CreatedUTC datetime declare  BackupUtc datetime   SELECT TOP 500  table02 Column CreatedUTC AS DeviceCreated  CAST DATEDIFF day  table02 Column CreatedUTC  table03 Column EndDateUTC  AS nvarchar 5      Days  As DaysActive  table03 Column EndDateUTC AS LastCompleteBackup  FROM  Operations table01 AS table01  LEFT OUTER JOIN      dbo table02 AS table02 ON     table02 Column KeyField   table01 Column KeyField  LEFT OUTER JOIN       dbo table03 AS table03 ON     table01 Column KeyField   table03 Column KeyField  Where table03 Column EndDateUTC  gt  dateadd hour  -24  getutcdate    --Gathers records with an end date in the last 24 hours AND table02  Column CreatedUTC    COALESCE  CreatedUTC  table02  Column CreatedUTC   AND table03  Column EndDateUTC    COALESCE  BackupUTC  table03  Column EndDateUTC    GROUP BY table03 Column EndDateUTC  table02 Column CreatedUTC ORDER BY table02 Column CreatedUTC ASC  DaysActive  table03 Column EndDateUTC DESC   The Output will be as follows    DeviceCreated    DaysActive    LastCompleteBackup  ---------------------------------------------------------  2 13 12 16 04    463 Days      5 21 13 12 14   2 12 13 22 37    97 Days       5 20 13 22 10

User · Answer

DECLARE   StartDate datetime    10 01 2012 08 40 18 000            EndDate   datetime    10 10 2012 09 52 48 000            DaysDifferent int   0           Sec BIGINT  select  Sec   DateDiff s   StartDate   EndDate   IF  DATEDIFF day   StartDate   EndDate   gt  0      BEGIN         select  DaysDifferent   DATEDIFF day   StartDate   EndDate          select  Sec    Sec -    DaysDifferent   86400           SELECT LTRIM STR  DaysDifferent 3     d    LTRIM STR  Sec 3600  5           RIGHT  0    LTRIM  Sec 3600 60   2          RIGHT  0    LTRIM  Sec 60   2  AS  dd hh mm ss      END ELSE     BEGIN         SELECT LTRIM STR  DaysDifferent 3     d    LTRIM STR  Sec 3600  5           RIGHT  0    LTRIM  Sec 3600 60   2          RIGHT  0    LTRIM  Sec 60   2  AS  dd hh mm ss      END  ---------------------------------------------------------------------------------- dd HH MM SS 9d 1 12 30

User · Answer

If you re not opposed to implicit type casting I ll offer this an alternative solution  Is it more readable with better formatting   You be the judge   DECLARE   StartDate datetime    10 01 2012 08 40 18 000            EndDate   datetime    10 04 2012 09 52 48 000   SELECT     STR ss 3600  5          RIGHT  0    LTRIM ss 3600 60   2          RIGHT  0    LTRIM ss 60   2  AS  hh mm ss  FROM  VALUES DATEDIFF s   StartDate   EndDate    seconds  ss

User · Answer

Try this one   declare  StartDate datetime   EndDate datetime  select  StartDate    2016-05-04 10 23 41 083    EndDate  2016-05-04 10 25 26 053   select CAST DateDiff MI   startDate   EndDate  60 AS varchar      Cast DateDiff MI   startDate   EndDate  60 AS varchar      cast DateDiff s   startDate   EndDate  60 AS varchar  as  hh mm ss

User · Answer

The shortest code would be    Select CAST   EndDateTime- StartDateTime  as time 0     hh mm ss

User · Answer

declare  StartDate datetime  declare  EndDate datetime  select  StartDate    10 01 2012 08 40 18 000   select  EndDate  10 04 2012 09 52 48 000   select  cast datediff hour  StartDate  EndDate  as varchar 10     left right cast cast cast   EndDate- StartDate  as datetime  as time  as varchar 16   14  6

User · Answer

declare  StartDate datetime   EndDate datetime  select  StartDate    10 01 2012 08 40 18 000   EndDate  10 04 2012 09 52 48 000   select convert varchar 5  DateDiff s   startDate   EndDate  3600      convert varchar 5  DateDiff s   startDate   EndDate  3600 60      convert varchar 5   DateDiff s   startDate   EndDate  60   as  hh mm ss    This query will helpful to you

User · Answer

While maybe not the most efficient  this would work   declare  StartDate datetime   EndDate datetime  select  StartDate    10 01 2012 08 40 18 000   EndDate  10 04 2012 09 52 48 000   select convert varchar 5  DateDiff s   startDate   EndDate  3600      convert varchar 5  DateDiff s   startDate   EndDate  3600 60      convert varchar 5   DateDiff s   startDate   EndDate  60     if you can run two selects then this would be better because you only do the datediff once   declare  StartDate datetime   EndDate datetime  select  StartDate    10 01 2012 08 40 18 000   EndDate  10 04 2012 09 52 48 000  declare  Sec BIGINT  select  Sec   DateDiff s   startDate   EndDate   select convert varchar 5   sec 3600      convert varchar 5   sec 3600 60      convert varchar 5    sec 60

User · Answer

DECLARE  dt1 datetime  2012 06 13 08 11 12    dt2 datetime  2012 06 12 02 11 12   SELECT CAST   dt2- dt1  as time 0

User · Answer

If you need a zero padded difference between 2 dates   SELECT convert varchar 2  FORMAT DATEDIFF s   startDate   endDate  3600  0                 convert varchar 2  FORMAT DATEDIFF s   startDate   endDate  3600 60  0             convert varchar 2  FORMAT DATEDIFF s   startDate   endDate  60  0     AS Duration

User · Answer

I like the idea of making this into a function so it becomes re-useable and your queries become much easier to read   --get the difference between two datetimes in the format   h m s  CREATE FUNCTION getDateDiff  startDate DATETIME   endDate DATETIME  RETURNS VARCHAR 10  AS BEGIN     DECLARE  seconds INT   DATEDIFF s   startDate   endDate      DECLARE  difference VARCHAR 10        CONVERT VARCHAR 4    seconds   3600              CONVERT VARCHAR 2    seconds   3600   60              CONVERT VARCHAR 2    seconds   60      RETURN  difference END   Usage   DECLARE  StartDate DATETIME    10 01 2012 08 40 18 000  DECLARE  endDate DATETIME    10 04 2012 09 52 48 000   SELECT dbo getDateDiff  startDate   endDate  AS DateDifference   Result       DateDifference 1   73 12 30   It s also easier to read the result if you add padding so the format is always hh mm ss   For example  here s how you would do that in SQL Server 2012 or later   --get the difference between two datetimes in the format   hh mm ss  CREATE FUNCTION getDateDiff  startDate DATETIME   endDate DATETIME  RETURNS VARCHAR 10  AS BEGIN     DECLARE  seconds INT   DATEDIFF s   startDate   endDate      DECLARE  difference VARCHAR 10        FORMAT  seconds   3600   00               FORMAT  seconds   3600   60   00               FORMAT  seconds   60   00       RETURN  difference END   Note that this will not clip the hour if it is more than 2 digits long   So 1 hour would show up as 01 00 00 and 100 hours would show up as 100 00 00

User · Answer

It s A Script Write Copy then write in your script file and change  your requered field and get out put  DECLARE  Sdate DATETIME   Edate DATETIME   Timediff VARCHAR 100  SELECT  Sdate    02 12 2014 08 40 18 000   Edate  02 13 2014 09 52 48 000  SET  Timediff DATEDIFF s   Sdate   Edate  SELECT CONVERT VARCHAR 5   Timediff 3600      convert varchar 5   Timediff 3600 60      convert varchar 5   Timediff 60  AS TimeDiff

[sql-server] SQL time difference between two dates result in hh:mm:ss

Examples related to sql-server

Examples related to tsql