1

How to subtract 2 dates in SQL and get HH:MI:SS

(SQL Server 2005) Iam using MS Access to do this.

Example:

23-09-2013 15:43:59

23-09-2013 15:43:33 -

Wanted answer 00:00:26

tezzo
  • 10,858
  • 1
  • 25
  • 48
Erwin
  • 13
  • 2

2 Answers2

0

Use DateDiff, for the timespan format use string methods:

SELECT  Diff = 
       right('0'+ rtrim(CAST(DateDiff(hour, @dt2, @dt1)  AS VARCHAR(2))), 2) + ':' +
       right('0'+ rtrim(CAST(DateDiff(minute, @dt2, @dt1)AS VARCHAR(2))), 2) + ':' +
       right('0'+ rtrim(CAST(DateDiff(second, @dt2, @dt1)AS VARCHAR(2))), 2)

Demo

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Iam new on this forum. So i don`t know how this works. But i will try to do it right. Im also a beginner in SQL. I tried this code : SELECT Diff = right('0'+ rtrim(CAST(DateDiff(hour, FirstAnswerDate, StartDate)AS VARCHAR(2))), 2) + ':' + right('0'+ rtrim(CAST(DateDiff(minute, FirstAnswerDate, StartDate)AS VARCHAR(2))), 2) + ':' + right('0'+ rtrim(CAST(DateDiff(second, FirstAnswerDate, StartDate)AS VARCHAR(2))), 2) FROM EmergencyCdr WHERE FirstAnswerDate is NOT NULL; I get this error: Syntax error (Operator is missing) in query expression Diff = ... – Erwin Sep 24 '13 at 10:11
  • @Erwin: at first sight your query looks ok, here is it(without the table but with local variables): http://sqlfiddle.com/#!6/d41d8/7660/0 – Tim Schmelter Sep 24 '13 at 10:12
  • I see your code works perfectly. But for some reason when i use it in MS Access it doesn`t work. This Access database is linked with a SQL 2005 server. I think the SQL version is the problem do you agree? Or maybe it is not T-SQL? What do you think? – Erwin Sep 24 '13 at 10:31
  • @Erwin: MS Access and Sql-Server and two completely different dbms. However,i'm not so familiar with MS access, therefore i don't know how you can translate this into valid sql in access. – Tim Schmelter Sep 24 '13 at 10:57
  • The SQL 2005 DB is loaded in MS Access. But maybe i can get access(i do not mean MS Access here) to the SQL 2005 DB. So i can use the query directly in the right database. Maybe that will work. I know your code works so i will try to make it work in my situation. Sadly i don`t have enough points to vote you up. You helped me very well. Thanks! – Erwin Sep 24 '13 at 11:10
  • I know your answer is good because i tried it in SQL Fiddle. I also tried tezzo his answer that works also. But i will mark your answer as the right answer because you helped me more. I will try to get to the SQL Database. If i have access to it i think i can make it work. So thank you for everything. – Erwin Sep 24 '13 at 11:34
0

If the two dates are in a range of 24 hours you can use this code:

SELECT LEFT(CONVERT(VARCHAR, DATEADD(SECOND, DATEDIFF(SECOND, @Date2, @Date1), 0), 114), 8)

Source: How to convert Seconds to HH:MM:SS using T-SQL

If you can't use native SQL query in your Access program, give a look at these links (Access syntax):

Maybe you can manipulate the resulting string without using CONVERT (not supported in Access).

Community
  • 1
  • 1
tezzo
  • 10,858
  • 1
  • 25
  • 48
  • Thank you for your response. I tried this and this is what i got: The expression contains an undefined function CONVERT. Is it possible that my SQL version is to old or that i don`t have T-SQL? What do you think? – Erwin Sep 24 '13 at 10:36
  • I tried your code in SQL Fiddle and it worked great! But in my situation it doesn`t work. But your code is fine. So thank you i wanted to upvote you but don`t have enough rep. points. – Erwin Sep 24 '13 at 11:38
  • The problem is that you use Access so you can't use native SQL syntax. I've just updated my answer. Good luck. – tezzo Sep 24 '13 at 12:35