Search code examples
sqlsql-server-2008timessrs-2008

calculate difference between time from 2 tables and column have datatype as smalldatetime and time

I have 2 table In and shift. Shift table is like

Starttime   |   Endtime     |   shift   |
---------------------------------------------------------       
07:00:00.00 |   16:00:00.00 |   1   |

In table is like

In          |   Out         |   shift   |   
------------------------------------------------------------
2016-07-01 06:54:00 |   2016-07-01 17:03:00 |   1   |

I want to calculate the difference between starttime and out something like

Output  
------  
01.03

01.03 is the difference of time between end time and the out time.

I wanted to do it in SSRS and when I am doing this in the output field it is giving me an error

=Fields!out.Value-Fields!endtime.Value

Can we do it using SQL query?

Endtime datatype is time and out datatype is smalldatetime

Solution

  • Use (Out-Endtime) in your SQL query to calculated required difference, based on table structure provided by you, your query should be like as given below :

    SELECT *,CONVERT(VARCHAR(10),(Out-Endtime),108)DIFF FROM tblIN I JOIN tblShift S ON I.shift=S.shift

    Result

    Starttime           Endtime            shift    In                   Out               shift    DIFF
07:00:00.0000000    16:00:00.0000000    1       2016-07-01 06:54:00 2016-07-01 17:03:00 1       01:03:00