DATEDIFF format

• yujinagata72

  Ten Centuries

  Points: 1072

  More actions

  August 1, 2016 at 11:16 pm

  #315765

  Hi,

  I use the below code, I can get the duration as hh:mm:ss.0000000.

  CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, last_request_end_time, GETDATE()), 0), 114) AS Duration

  But how I can get the format like dd:hh:mm:ss?

  Thanks,

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  August 1, 2016 at 11:41 pm

  #1893107

  Quick suggestion

  😎

  USE TEEST;

  GO

  SET NOCOUNT ON;

  --

  -- DECLARE A DATETIME VARIABLE AND SET IT'S VALUE 03:31:35.2730000 BEFORE CURRENT DATE

  DECLARE @LAST_RQ_TIME DATETIME = DATEADD(MILLISECOND,-12695274,GETDATE());

  -- DECLARE A TIME(7) VARIABLE AND SET IT'S VALUE TO THE DIFFERENCE (DURATION) OF CURRENT AND THE DATETIME VARIABLE

  DECLARE @TIME_DIFF TIME(7) = DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,@LAST_RQ_TIME,GETDATE()),CONVERT(TIME,'00:00:00',0))

  SELECT

  @TIME_DIFF AS DURATION;

  Output

  DURATION

  ----------------

  03:31:35.2730000

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 2, 2016 at 2:04 am

  #1893141

  yujinagata72 (8/1/2016)

  ---

  Hi,

  I use the below code, I can get the duration as hh:mm:ss.0000000.

  CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, last_request_end_time, GETDATE()), 0), 114) AS Duration

  But how I can get the format like dd:hh:mm:ss?

  Thanks,

  You will need to use a DATETIME datatype if your requirement is to capture and retain values over 24 hours. Then use CONVERT to format the output.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden