0

SQL Server : I need to find difference between two times in the format of HH:MM and both columns having time datatype:

SELECT DATEDIFF(MINUTE, sh.ToTime, sh.FromTime)
FROM ShiftTimes sh 
INNER JOIN StdBreaksInShifts sb ON sb.ShiftId = sh.ShiftId

Table:

Fromdate                todate
----------------------------------------
08:15:00.0000000        16:30:00.0000000

Can anyone suggest how to do this?

Vali Maties
  • 379
  • 1
  • 2
  • 21
vikas kumbhar
  • 13
  • 4
  • Please add some sample data and add what you have tried so far. – iVad Feb 28 '20 at 07:27
  • Edit your question and add more details, maybe an example with some rows and which is the expected result. – Vali Maties Feb 28 '20 at 07:27
  • Does this answer your question? [SQL time difference between two dates result in hh:mm:ss](https://stackoverflow.com/questions/13577898/sql-time-difference-between-two-dates-result-in-hhmmss) – Sebastian Brosch Feb 28 '20 at 07:28
  • Show sample of ToTime column data and FromTime column data. See Convert function from MSSQL. – Vali Maties Feb 28 '20 at 07:31

1 Answers1

1

You can use CONVERT with style to get part of the corresponding format. Something like this:

DECLARE @FromTime TIME = '08:15:00.0000000'
       ,@ToTime TIME = '16:30:00.0000000';

SELECT @FromTime
      ,@ToTime
      ,CONVERT(VARCHAR(5), DATEADD(minute, DATEDIFF(minute, @FromTime, @ToTime), 0), 114)

08:15:00.0000000 16:30:00.0000000 08:15

gotqn
  • 42,737
  • 46
  • 157
  • 243