asked on 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!
GETDATE() - MaxEnteredOn = d.hh.n.ss
Thanks for the help!
Microsoft SQL ServerSQL
Last Comment
Jeremy Campbell
ASKER
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.
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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!
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!
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...
...previous same as before...
MaxEnteredOn,
CASE WHEN days_diff = 0 THEN '' ELSE CAST(days_diff AS varchar(4)) + '.' END +
...remaining same as before...
ASKER
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.
Ex.
2.18:24:30
0.01:10:05
10.22:01:00
It should look like a stop watch.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That did the trick, thanks Scott!
(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