Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

A few quick time calculations

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

Filed under: Blog Tagged: syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...