Find Hours, Minutes and Seconds in between two Datetime

In one of the previous articles, we explored how to find the difference between two dates.

In this short snippet, let us see how to find the Hours, Minutes and Seconds in between two Datetime.

-- Find Hours, Minutes and Seconds in between two datetime
DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()

SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

11 comments:

Anonymous said...

I disagree.
DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/17/2008 08:00:00'
SET @Second = getdate()

SELECT DATEDIFF(hour,@First,@Second) as TotalHours,
DATEDIFF(minute,@First,@Second) - (DATEDIFF(hour,@First,@Second) * 60) as TotalMinutes,
DATEDIFF(second,@First,@Second) - (DATEDIFF(minute,@First,@Second) * 60) as TotalSeconds
would give a meaningful hours (plus) minutes (plus) seconds.
Leaving the subtraction out of the second and third columns would give independent totals, like 2 hours 178 minutes rather than 2 hours 58 minutes (with the subtraction).

Suprotim Agarwal said...

Hi,

Thanks for sharing the comment.
The sql snippet calucates hours, minutes and seconds irrespective of each other. That's why the column names are written as TotalHours, TotalMinutes, TotalSeconds. As you mentioned, if anyone wants to get independent totals, he should not subtract.

Anonymous said...

Hi,

Thanks it helps me lots. i whichever was searching i got it from this.

Anonymous said...

if this is independent, it wont work. example is 1:59 am and 2:15 am

Anonymous said...

I do not think the author intended to make it work on just the time. I guess the sample as a whole works out quiet well!!

Anonymous said...

If you want the difference between two datetimes calculated into different columns, here is a much faster way:

floor(DATEDIFF(ss,date1,date2)/3600) as Hours,
floor(DATEDIFF(ss,date1,date2)/60)- floor(DATEDIFF(ss,date1,date2)/3600)*60 as Minutes,
floor(DATEDIFF(ss,date1,date2))- floor(DATEDIFF(ss,date1,date2)/60)*60 as TotalSeconds

Peeyush Agrawal said...
This comment has been removed by the author.
Peeyush Agrawal said...

Try this UDF to calucalte the exact difference:

http://www.sqlmag.com/Article/ArticleID/48764/sql_server_48764.html

Anonymous said...

did anyone understand the code at http://www.sqlmag.com/Article/ArticleID/48764/sql_server_48764.html ??

the code has something called callout A, B, C etc...

does anyone know what this is

Anonymous said...

What is that link got to do with this post?

mrhassell said...

Why not just use the ss parameter or any of the other valid options and use date1 / date 2 in a single select am I mising something, like the point to all the FLOOR and math?

DATEDIFF(ss, [CreateDateTime],[ActionUpdateDateTime]) AS [ExecutionSeconds]

year
yy, yyyy

quarter
qq, q

month
mm, m

dayofyear
dy, y

day
dd, d

week
wk, ww

hour
hh

minute
mi, n

second
ss, s

millisecond
ms

microsecond
mcs

nanosecond
ns