Hello Friends, In below given query i want the Aging column's outputs in DD:HH:MM format. I am unable to get it. Could any of you help?

Question

Select Work_Order_ID,summary,ISNULL(Request_Assignee, 'Unassigned') as Request_Assignee,

CAST(DATEADD(s, Submit_Date, 'Jan 1, 1970') as nvarchar) as Submit_Date ,

CAST(DATEADD(s, Last_Modified_Date, 'Jan 1, 1970') as nvarchar) as Last_Modified_Date,

DATEDIFF(HOUR, CAST(DATEADD(s, Submit_Date, 'Jan 1, 1970') as nvarchar), CAST(GetUTCDate() AS DATE) ) 'SubmitAging(DD:HH:MM)',
DATEDIFF(HOUR, CAST(DATEADD(s, Last_Modified_Date, 'Jan 1, 1970') as nvarchar), CAST(GetUTCDate() AS DATE) )  'LastModifiedAging(DD:HH:MM)',

 Last_Modified_By,Submitter 
 from WOI_WorkOrder
where (WOI_WorkOrder.Support_Group_Name = 'DBA' OR WOI_WorkOrder.ASGRP = 'DBA') and WOI_WorkOrder.status<5

-------------------------------------------------------OUTPUT------------------------------------------------------------

Work_Order_ID	summary	Request_Assignee	Submit_Date	Last_Modified_Date	SubmitAging(DD:HH:MM)	LastModifiedAging(DD:HH:MM)	Last_Modified_By	Submitter
WO0000000868982	DB:Take db backup	Rishabh Thakur	Nov 24 2019  3:33AM	Apr  5 2020 10:57PM	6357	3146	abpatil	AR_ESCALATOR
WO0000000877368	DB:Takedb backup of ar and smartreporting db	Rishabh Thakur	Dec 30 2019  2:41AM	Apr  5 2020  8:27PM	5494	3148	abpatil	AR_ESCALATOR

Answer 1

Hi Abhijit,

SELECT FORMAT(GETDATE(),'d:HH:MM') AS DATUMUNDZEIT

DATUMUNDZEIT
15:19:08

Regards

Answer 2

Try using minutes instead of hours, and a custom function:

   select … dbo.FormatDDHHMM( DATEDIFF(minute, … )) 'SubmitAging(DD:HH:MM)'

where the function is:

create function dbo.FormatDDHHMM( @totalMinutes int )
returns varchar(30)
begin
	declare @minutes int = @totalMinutes % 60
	declare @hours int = @totalMinutes / 60 % 24
	declare @days int = @totalMinutes / (60 * 24)

	return concat( format( @days, '00'), ':', format( @hours, '00'), ':', format( @minutes, '00'))
end

Answer 3

Hi Abhijit Anil Patil,

Use the FORMAT function for locale-aware formatting of date/time and number values as strings

DECLARE @d DATETIME = GETDATE();  
SELECT GETDATE() currenttime
SELECT FORMAT( @d, 'dd:HH:MM') AS 'DateTime Result'  

More details please refer to:FORMAT (Transact-SQL)

Best Regards 
Echo   

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 4

Hi Abhijit Anil Patil,

Has your problem been solved? If it is solved, please mark the point that you 
think is correct as an answer. This can help others who encounter similar problems.

Best Regards 
Echo   

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.