How to calculate Time Diff

Transact-SQL
1

hello,

I have used below conversion for date-time to find time from date.

LTRIM(RIGHT(Convert(VARCHAR(20), PGL.[In-Time],100),7))as In_Time,
LTRIM(RIGHT(Convert(VARCHAR(20), PGL.[Out-Time],100),7)) as Out_Time

Now I have to calculate time difference? How can I do it?

please suggest....

2

I'm not sure how you want it : in minutes or in seconds or in miliseconds ..

I hope the next script cover more of the options:

 declare 
    @dtIn AS DATETIME = '2015-06-24 07:26:58'
    ,@dtOut AS DATETIME  = '2015-06-24 08:24:58'



SELECT
    CONVERT(VARCHAR(30),@dtIn,114) as varcharVersionIn
    ,CONVERT(VARCHAR(30),@dtOut,114) as varcharVersionOut
    ,CONVERT(TIME,@dtIn,114) as timeVersionIn
    ,CONVERT(TIME,@dtOut,114) as timeVersionOut
    ,DATEDIFF(second,CONVERT(TIME,@dtOut,114),CONVERT(TIME,@dtIn,114)) NoSeconds1

    ,[ss] = DATEDIFF(ss,@dtIn,@dtOut)
    ,[days] = DATEDIFF(ss,@dtIn,@dtOut) / 86400 -- 86400=number of seconds in 1day
    ,[hours] = (DATEDIFF(ss,@dtIn,@dtOut) % 86400) / 3600  --3600=number of seconds in 1hour
    ,[minutes]= ((DATEDIFF(ss,@dtIn,@dtOut) % 86400) % 3600 ) / 60  -- 60 = numbers of seconds in 1mi
    ,[seconds]= ((DATEDIFF(ss,@dtIn,@dtOut) % 86400) % 3600 ) % 60 



varcharVersionIn    varcharVersionOut    timeVersionIn    timeVersionOut    NoSeconds1    ss    days    hours    minutes    seconds
07:26:58:000    08:24:58:000    07:26:58.0000000    08:24:58.0000000    -3480    3480    0    0    58    0
3

I want it in HH:MM:SS in one column

Eg

In time out time time diff
09:00:00AM 12:00:00 03:00:00

4

convert(varchar(8), dateadd(second, datediff(second, TIME_IN, TIME_OUT), 0), 108)

5

Thanks all,

I also got the solution

CONVERT(VARCHAR(8),DATEADD(MINUTE,DATEDIFF(MINUTE,In-Time,Out-Time),'2011-01-01 00:00:00:000'),114)as Time_Diff

6

WHAT are the datatypes for the PGL.[In-Time] and PGL.[Out-Time] columns? Are they actually the DATETIME datatype?