How can I perform DateDiff on two datetime fields in SQL Server Management Studio and display as an output of d.hh:mm:ss?

How can I perform DateDiff on two datetime fields in SQL Server Management Studio and display as an output of d.hh:mm:ss?

GETDATE()  - MaxEnteredOn = d.hh.n.ss

Thanks for the help!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT MachName,
      StateName,
      MaxEnteredOn,
    CAST(days_diff AS varchar(4)) + '.' +
    RIGHT('0' + CAST(seconds_diff2 / (60 * 60) AS varchar(2)), 2) + '.' +
    RIGHT(CAST(seconds_diff2 % (60 * 60) / 60 AS varchar(2)), 2) + '.' +
    RIGHT(CAST(seconds_diff2 % 60 AS varchar(2)), 2)
      AS SCLU,
      StateColorR,
      StateColorG,
      StateColorB
      FROM [sapp0].[PartQueue].[dbo].[tblMemexStates] ms
      INNER JOIN [sapp0].[AccessSystem].[dbo].[tblStateColors] sc
      ON ms.StateName = sc.StateColorID
     CROSS APPLY (
        SELECT DATEDIFF(SECOND, DATEADD(HOUR, -5, MaxEnteredOn), GETDATE()) AS seconds_diff
    ) AS alias1
      CROSS APPLY (
        SELECT seconds_diff / (60 * 60 * 24) AS days_diff,
            seconds_diff % (60 * 60 * 24) AS seconds_diff2
    ) AS alias2
     WHERE ms.MachName = @MemexMachineName
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Where the date/datetime in single quotes is the older date...

SELECT CAST(dtDiffDay as nvarchar) + '.' + CAST(dtDiffHour as nvarchar) + ':' + CAST(dtDiffMinute as nvarchar)  + ':' + CAST(dtDiffSecond as nvarchar) FROM
(SELECT DATEDIFF(DAY, '1/1/2018', GETDATE()) as dtDiffDay,
DATEDIFF(DAY, DATEPART(hour, '1/1/2018'), DATEPART(hour, GETDATE())) as dtDiffHour,
DATEDIFF(DAY, DATEPART(MINUTE, '1/1/2018'), DATEPART(MINUTE, GETDATE())) as dtDiffMinute,
DATEDIFF(DAY, DATEPART(SECOND, '1/1/2018'), DATEPART(SECOND, GETDATE())) as dtDiffSecond) AS qryInner
0
 
Jarred MeyerProduction ManagerAuthor Commented:
How do I incorporate that into my existing query?

Here is my current code which seems to work with the exception that I would like the format to always be 0.00:00:00 and it will sometimes display single digits.


	SELECT MachName, 
	StateName, 
	MaxEnteredOn,
	DATEADD("hh",-5,MaxEnteredOn) AS DayLightSavingsAdjusted,
	CAST(DATEDIFF("ss",DATEADD("hh",-5,MaxEnteredOn),GETDATE()) / 86400 AS varchar(4)) + '.' 
	+ CAST(DATEDIFF("ss",DATEADD("hh",-5,MaxEnteredOn),GETDATE()) / 3600 AS varchar(4)) + ':' 
	+ CAST(DATEDIFF("ss",DATEADD("hh",-5,MaxEnteredOn),GETDATE()) / 60 - (((DATEDIFF("ss",DATEADD("hh",-5,MaxEnteredOn),GETDATE()) / 3600)) * 60) AS varchar(4)) + ':' 
	+ CAST(DATEDIFF("ss",DATEADD("hh",-5,MaxEnteredOn),GETDATE()) - (((DATEDIFF("ss",DATEADD("hh",-5,MaxEnteredOn),GETDATE()) / 60)) * 60) AS varchar(4))
	AS SCLU,
	StateColorR, 
	StateColorG, 
	StateColorB 
	FROM [sapp0].[PartQueue].[dbo].[tblMemexStates] ms 
	INNER JOIN [sapp0].[AccessSystem].[dbo].[tblStateColors] sc
	ON ms.StateName = sc.StateColorID
	WHERE ms.MachName = @MemexMachineName

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jarred MeyerProduction ManagerAuthor Commented:
Thanks Scott definitely is pretty much there. The only thing left would be to pad the Hours, Minutes & Seconds with 0's. I don't want to have a leading 0 for the days.

So 0.00.00.00

It looks like you hardcoded a 0 in the front of the hours but what happens if it goes into double digits? Basically I just always want it to display in two digits for the hours, minutes and seconds.

Thanks!
0
 
Scott PletcherSenior DBACommented:
I didn't hard-code a zero, but a zero will result when it's converted to varchar.  To get rid of the zero, do this:
...previous same as before...
      MaxEnteredOn,
  CASE WHEN days_diff = 0 THEN '' ELSE CAST(days_diff AS varchar(4)) + '.'  END +
...remaining same as before...
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Sorry, I may not have been clear.. I don't want the digits to disappear if no value exists. I just want there to always be two digits displayed for Hours, Minutes & Seconds. Days can continue display as 1 digit, unless it happens to be more than 9.

Ex.

2.18:24:30
0.01:10:05
10.22:01:00

It should look like a stop watch.
0
 
Scott PletcherSenior DBACommented:
D'oh, I just accidentally left off the " '0' + " on the mins and secs, just a typo/"copy-and-paste-o":

    CAST(days_diff AS varchar(4)) + '.' +
    RIGHT('0' + CAST(seconds_diff2 / (60 * 60) AS varchar(2)), 2) + '.' +
    RIGHT('0' + CAST(seconds_diff2 % (60 * 60) / 60 AS varchar(2)), 2) + '.' +
    RIGHT('0' + CAST(seconds_diff2 % 60 AS varchar(2)), 2)
0
 
Jarred MeyerProduction ManagerAuthor Commented:
That did the trick, thanks Scott!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.