Click here to Skip to main content
Click here to Skip to main content

Working with durations in MSSQL server

, 31 Jan 2010
Rate this:
Please Sign up or sign in to vote.
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...
In dealing with durations in MSSQL server, I have found the following three resources helpful:
 
Thumbs Up | :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

Thumbs Up | :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
 
Thumbs Up | :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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Ilka Guigova
Software Developer
Canada Canada

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 31 Jan 2010
Article Copyright 2010 by Ilka Guigova
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid