Have you ever needed to do a quick time calculations of the amount of hours/minutes/seconds that have passed? Suppose you needed to get the total number of minutes that have passed for a total time of ‘2:24’.
There are some easy ways to do this, and the normal calculation that you might make is to multiple hours by 60 and then add minutes, so something like:
DECLARE @t TIME, @n INT SELECT @t = '2:24' SELECT @n = DATEPART( hh, @t) * 60 + DATEPART(mi, @t) SELECT @n
That returns 144, which is the correct value (60 * 2 = 120, adding 24). However there’s an easier, and cleaner, way.
SELECT DATEDIFF(mi, 0, @t)
You can let SQL Server do the math, grabbing the DATEDIFF function and using 0 as a starting point.
Number of seconds in a day?
DECLARE @t TIME, @n INT, @d DATETIME SELECT @t = '11:59:59PM' SELECT DATEDIFF(ss, 0, @t) + 1
Voice of the DBA 
Comments
2 responses to “A few quick time calculations”
That last query is a little off the mark, returning less than half the correct result (86400). You could use an alternative shortcut query like this:
`SELECT DATEDIFF(ss, 0, 1)
or just *know* the answer.
LikeLike
Good catch, I forgot the PM, and the +1. Darn 24 hour clocks
LikeLike