Thursday, May 21, 2009

Time Conversion

 
--Convert Seconds to Hours
SELECT CONVERT(VARCHAR(10), DATEADD(second, 3614, 0), 108) AS HrsFormat

--Convert Seconds to Hrs format
SELECT CONVERT(VARCHAR(10), DATEADD(second, 4320, 0) , 8) AS HrsFormat

--Convert hours to MilliSeconds
SELECT SUM(DATEDIFF(millisecond,0,CAST('01:12:00' AS DATETIME))) AS MilliSeconds

--Convert MilliSeconds to Seconds
SELECT CONVERT(DECIMAL(20,0), DATEDIFF(millisecond,0,CAST('01:12:00' AS DATETIME)) / 1000.00) AS Seconds

--Convert MilliSeconds to Minutes
SELECT (CONVERT(DECIMAL(20,0), DATEDIFF(millisecond,0,CAST('01:12:00' AS DATETIME)) / 1000.00))/60 AS Minutes

--Convert Seconds to Minutes
SELECT (4320 / 60) AS TotalDurationInMinutes

--Convert hours to minutes
SELECT (DATEDIFF(SECOND, '00:00:00', '01:12:00') / 60.0E0) AS [Minutes]

--Convert Minutes to Hours
SELECT CONVERT(varchar, FLOOR(72 / 60.0)) + ':' + RIGHT('0' + CONVERT(varchar, 72 % 60), 2) AS HrMin

--1 Minute = 0.0166666666666667 Hours

No comments: