كيفية تحويل حقل الوقت من أرقام الى وقت بقاعدة البيانات

edit

تم إستخدام هذه المعادلة

select tl.fromDate,tl.fromTime,right('00'+cast(cast(((tl.fromTime+0.0)/3600000) as int) as varchar(2)),2)+':'+right('00'+cast(cast(Round((((tl.fromTime+0.0)/3600000)-cast( (tl.fromTime+0.0)/3600000 as int))*60,0) as int) as varchar(2)),2) fTime
from TimeAttendanceLine tl

و قامت بالتالى


إستعلام كامل لحساب الفرق أيضا:

select tl.fromDate,right('00'+cast(cast(((tl.fromTime+0.0)/3600000) as int) as varchar(2)),2)+':'+right('00'+cast(cast(Round((((tl.fromTime+0.0)/3600000)-cast( (tl.fromTime+0.0)/3600000 as int))*60,0) as int) as varchar(2)),2) fTime
      ,tl.toDate,right('00'+cast(cast(((tl.toTime+0.0)/3600000) as int) as varchar(2)),2)+':'+right('00'+cast(cast(Round((((tl.toTime+0.0)/3600000)-cast( (tl.toTime+0.0)/3600000 as int))*60,0) as int) as varchar(2)),2) tTime
      ,right('00'+cast(cast(((tl.toTime-tl.fromTime+0.0)/3600000) as int) as varchar(2)),2)+':'+right('00'+cast(cast(Round((((tl.toTime-tl.fromTime+0.0)/3600000)-cast( (tl.toTime-tl.fromTime+0.0)/3600000 as int))*60,0) as int) as varchar(2)),2) spentTime
from TimeAttendanceLine tl