Working with durations in MSSQL server

In dealing with durations in MSSQL server, I have found the following three resources helpful: :thumbsup: If you need to report a duration in a certain granularity such as "Task runs for ## minutes ## seconds", then refer to this[^] post:

/*
CREATE TABLE Tasks
(
	ID INT IDENTITY PRIMARY KEY,
	START_TIME DATETIME NOT NULL,
	END_TIME DATETIME NOT NULL
)
GO
INSERT INTO Tasks (START_TIME, END_TIME)
SELECT '2007-01-01 6:34:12 AM', '2007-01-01 12:45:34 PM' UNION ALL
SELECT '2007-01-02 9:23:08 AM', '2007-01-02 5:05:37 PM' UNION ALL
SELECT '2007-01-03 4:34:12 PM', '2007-01-03 4:55:18 PM' UNION ALL
SELECT '2007-01-04 11:02:00 AM', '2007-01-05 2:53:21 PM' UNION ALL
SELECT '2007-01-05 7:52:55 AM', '2007-07-05 9:08:48 AM' UNION ALL
SELECT '2007-01-06 7:59:11 PM', '2010-01-07 1:23:11 AM' UNION ALL
SELECT '2008-12-31 18:20', '2009-01-01 17:20'
GO
*/
-- DURATIONS http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
SELECT *,
       (TOTAL_SS) / 31536000 AS YY,
       DATEDIFF(MONTH, 0, END_TIME - START_TIME) % 12 AS MM,
       (TOTAL_SS % 31536000) / 604800 AS WW,  -- 604800 = 7 * 24 * 60 * 60
       (TOTAL_SS % 31536000) / 86400 AS DD,   -- 3153600 = 365 * 24 * 60 * 60
       (TOTAL_SS % 86400) / 3600 AS HH,       -- 86400 = 24 * 60 * 60
       (TOTAL_SS % 3600)/60 AS MI,            -- 3600 = 60 * 60
       (TOTAL_SS % 60) AS SS
FROM   ( -- DATEDIFF (Transact-SQL) / see the comment at the bottom of the page: http://msdn.microsoft.com/en-us/library/ms189794.aspx
         SELECT   START_TIME,
                  END_TIME,
                  END_TIME - START_TIME AS TOTAL,
                  DATEDIFF(ss, 0, END_TIME - START_TIME) AS TOTAL_SS
            FROM  Tasks
            WHERE END_TIME >= START_TIME
        ) AS Q1

:thumbsup: If you need to report a duration in a certain format such as "## hours 00 minutes 00 seconds", then you may need to pad the number of minutes and seconds to ensure that, for example, 3 minutes is represented as 03. Padding for integers is explained here[^]:

-- PADDING http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/29/765.aspx
DECLARE @CH CHAR(1) = '0', 
        @LEN INT = 2, 
        @N INT = 1
SELECT CASE WHEN @LEN > LEN(@N) THEN REPLICATE(@CH, @LEN - LEN(@N)) ELSE '' END + CAST(@N AS VARCHAR) AS PADDED_NUMBER

:thumbsup: If you need to extract the date part of a DATETIME timestamp, you can use the fact that internally dates are treated as FLOAT as described in more detail here[^]:

-- DATE ONLY http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm 

SELECT CAST (FLOOR(CAST (GETDATE() AS FLOAT)) AS DATETIME) AS DATE_ONLY