# How to derive exact days, hours and minutes between two dates.

 0 Given two datetimes, how can I query, using t-sql, the exact day, hour and minute difference between them? For example, d1 = 2011-02-08 07:52 d2 = 2011-02-14 15:34 The correct answer is 5 days, 7 hours and 42 minutes. Using Datediff with dd, hour, minute arguments returns 6 days. Thank you for help. more ▼ asked Feb 14, 2011 at 03:56 PM in Default mzp 31 ● 3 ● 3 ● 3 Fatherjack ♦♦ 42.8k ● 75 ● 79 ● 108 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 The difference between February 8th at 7:52 AM and February 14th at 3:34 PM is indeed 6 days, 7 hours, and 42 minutes. ``````2/8 @ 07:52 - 2/9 @ 07:52 = 1 day 2/9 @ 07:52 - 2/10 @ 07:52 = 1 day 2/10 @ 07:52 - 2/11 @ 07:52 = 1 day 2/11 @ 07:52 - 2/12 @ 07:52 = 1 day 2/12 @ 07:52 - 2/13 @ 07:52 = 1 day 2/13 @ 07:52 - 2/14 @ 07:52 = 1 day 2/14 @ 07:52 - 2/14 @ 15:34 = 7 hours, 42 minutes ``````Check out Jeff Moden's outstanding post on time differences for a way to calculate the difference in various formats. He did a great job explaining the concept, I think. Later on in the thread, GSquared has another method for getting the values, and it looks like this: ``````declare @BeginDate datetime = '2011-02-08 07:52'; declare @EndDate datetime = '2011-02-14 15:34'; select datepart(year, @EndDate - @BeginDate)-1900 as years, datepart(month, @EndDate - @BeginDate) - 1 as months, datepart(day, @EndDate - @BeginDate) - 1 as days, datepart(hour, @EndDate - @BeginDate) as hours, datepart(minute, @EndDate - @BeginDate) as minutes, datepart(second, @EndDate - @BeginDate) as seconds; ``````This query has some problems with edge cases, so beware if your query needs to span more than one year. more ▼ answered Feb 14, 2011 at 04:48 PM Kevin Feasel 6.1k ● 3 ● 5 ● 11 Wow! I'd lost track of that particular thread. Thanks, Kevin! Maybe I should turn it into an "SQL Spackle" article. Feb 14, 2011 at 06:21 PM Jeff Moden That might be a good one, yeah. I normally would use C# for date comparisons (that's what TimeSpan is for, after all), but this seems like a tailor-made short article: it won't come up often, but when it does, it's good to have something in the Briefcase that solves the problem. Feb 15, 2011 at 04:14 AM Kevin Feasel add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email: