0

I have two columns INTIME, OUTTIME as 09:03:01 as INTIME and 18:06:00 AS OUTTIME Datatype for these columns is varchar type now I want to calculate the duration between OUTTIME minus INTIME then I need to get duration as 9:02:59

user2477936
  • 57
  • 2
  • 5
  • 11
  • 1
    [Bad habits to kick : mis-handling date / range queries](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) - you should **not** store dates as strings - you're just asking for trouble that way. Use the `DATE` or `DATETIME` datatypes! – marc_s Aug 27 '13 at 05:06
  • 1
    possible duplicate of [SQL time difference between two dates result in hh:mm:ss](http://stackoverflow.com/questions/13577898/sql-time-difference-between-two-dates-result-in-hhmmss) – bgs Aug 27 '13 at 05:09

2 Answers2

1

Just substract them:

DECLARE @INTIME DATETIME, @OUTTIME DATETIME
SELECT @INTIME = CONVERT(DATETIME,'09:03:01'), @OUTTIME = CONVERT(DATETIME,'18:06:00')
SELECT CONVERT(VARCHAR(10),@OUTTIME - @INTIME,108)

Output: 09:02:59

Andrey Gordeev
  • 30,606
  • 13
  • 135
  • 162
0

try out this. may this will help you.

select CONVERT(CHAR(8),DATEADD(second,datediff(second, cast('09:03:01' as datetime),cast('18:06:00' as datetime)),0),108) as duration

Check out this online

SQL Fiddle Demo

AB Vyas
  • 2,349
  • 6
  • 26
  • 43