how to select OThours from intime and outtime

Question

I have a table in that table I have a column name like attdatein time out time like below format attdate in time out time 12-03-17. 08:45. 20.37 13-03-17. 08:35. 20:45 if out time greater than 18:00 calculate OThrs.which datatype I have to store OThrs.if OThrs >than 2.30 means round off to 3

Nilesh Shah · Answer

You can use datediff inbuilt function

siva nathan · Answer

can we use aggregate function for time column

Nilesh Shah · Answer

I suggest you don't store date and time separately in the column but keep only 2 fileds intime and outtime both as datetime columns. You can take the overtime column as Time datatype. For calculating overtime you can write an after update trigger on outtime column which will calculate the time difference and you apply all your business logic in this trigger. (like time greater than 18:00 round off etc).