Labels

ASP.NET (1) Data Model (1) Django (1) MDX (15) Python (3) Redshift (3) SSAS (15) SSRS (3) T-SQL (29)

Monday, 18 June 2012

SQL query to find the difference between two periods in hh:mm:ss format

DECLARE @StartTime DateTime = Getdate() -1, @EndTime DateTime = GetDate()

SELECTCONVERT(VARCHAR(6),DATEDIFF(SECOND, @STARTTIME, ISNULL(@ENDTIME,GETDATE()))/ 3600)

+':'+ RIGHT('0' + CONVERT(VARCHAR(6), (DATEDIFF(second, @STARTTIME, ISNULL(@ENDTIME,GETDATE())) % 3660) / 60), 2)

+':'+ RIGHT('0' + CONVERT(VARCHAR(5), DATEDIFF(second, @STARTTIME, ISNULL(@ENDTIME,GETDATE())) % 60),2) AS DURATION

No comments:

Post a Comment