如何在SQL Server中获取InTime和OutTime在凌晨12点后到达时的正确时间格式

Question

我已经尝试过打卡员工的打卡时间，我已经尝试了下面的查询，当打卡时间像上午10:00和打卡时间像下午6:00时，它工作得很好，并显示TotalDuration为8小时。

但是当像晚上9:00和凌晨5:00这样的时间打卡时，它会失败

关于这个问题，我做了基于打卡和打卡的计算，它显示了总小时数。

但是当用户在凌晨12点以后打卡时，在打卡输入和打卡输出上都显示为00:00，而TotalDuration显示为0

我得到的输出

    I:9:05AM\nO:5:32PM\nT:8:27 \nS :~Present On WeeklyOff - WeekOff,:Saturday,:    
    I:12:00AM\nO:12:00AM\nT:0:0  \nS :~Present On WeeklyOff - WeekOff,:Saturday,:

下面是我的问题：

SELECT 
    ROW_NUMBER() OVER (ORDER BY e.EmployeeCode,AttendanceDate DESC) AS 'SNo',  
    e.EmployeeCode AS 'EmpID',  
    e.EmployeeName AS 'EmpName',  
    d.DepartmentFName AS 'DeptName', 
    CONVERT(VARCHAR, a.AttendanceDate, 110) AS 'AttendanceDate',
    'I:' + LTRIM (RIGHT(CONVERT(VARCHAR(50),+'I:'+CONVERT(VARCHAR(50),+CONVERT(DATETIME, a.InTime,0)),100),7))+'\n'+'O:'+LTRIM (RIGHT(CONVERT(VARCHAR(20),CONVERT(DATETIME, CASE WHEN a.OutTime LIKE '%(%' THEN LEFT(a.OutTime, CHARINDEX('(', a.OutTime + ')') - 1)  
   ELSE a.OutTime END,0),100),7))+'\n'+ 'T:'+ CONVERT(CHAR(5),CAST(CAST(CASE WHEN  a.InTime> a.OutTime THEN DATEDIFF (MINUTE,CAST(a.InTime as datetime),DateAdd(day,1,CAST(a.OutTime AS datetime)))
 ELSE DATEDIFF(MINUTE, a.InTime , a.OutTime) END AS INTEGER)/60 AS VARCHAR)+':'+CAST(cast(CASE WHEN  a.InTime> a.OutTime THEN DATEDIFF (MINUTE,CAST(a.InTime as datetime),DateAdd(day,1,CAST(a.OutTime AS datetime)))
 ELSE DATEDIFF(MINUTE, a.InTime , a.OutTime) END AS INTEGER)%60 AS VARCHAR),108)+ '\n' + 'S :~' +  a.[Status]+ ' - ' + CASE WHEN a.[Status] = 'Present' THEN '' WHEN a.[Status] = 'On WeeklyOff' and la.LeaveType<>'' THEN la.LeaveType   
   WHEN DATENAME(dw, a.AttendanceDate)='Sunday' THEN 'WeekOff' WHEN DATENAME(dw, a.AttendanceDate)='SaturDay'   
   THEN 'WeekOff' ELSE la.LeaveType COLLATE SQL_Latin1_General_CP1_CI_AS   ENd + ',:'+DATENAME(dw, a.AttendanceDate) + ',:'+ CASE WHEN la.LeaveStatus is null THEN '' ELSE la.LeaveStatus END AS 'PunchDetails',
   '0LOP' AS 'NoOfLOP',
   '0WkEnd' AS 'NoOfWkEndWorked' ,ISNULL(la.LeaveStatus,'') AS 'LeaveStatus' 

-- INTO #Result02  
FROM   
    esslsmartofficenew.dbo.Employees e (NOLOCK) 
INNER JOIN
    esslsmartofficenew.dbo.AttendanceLogs a (NOLOCK) ON e.EmployeeId = a.EmployeeId 
INNER JOIN   
    esslsmartofficenew.dbo.Departments d (NOLOCK) ON d.DepartmentId = e.DepartmentId 
INNER JOIN  
    esslsmartofficenew.dbo.Shifts s (NOLOCK) ON s.ShiftId = a.ShiftId 
LEFT JOIN  
    IntranetReleaseNew.dbo.LeaveApplication la ON la.EmpId collate SQL_Latin1_General_CP1_CI_AS = e.employeecode collate SQL_Latin1_General_CP1_CI_AS 
                                               AND a.AttendanceDate >= la.leavefromdate 
                                               AND a.AttendanceDate <= la.leaveTodate  
WHERE   
    e.DepartmentId = '10' AND  
    e.EmployeeName NOT LIKE '%del_%' AND
    e.EmployeeCode LIKE '%S%' AND 
    e.EmployeeCode NOT LIKE '%Del_%' AND
    a.AttendanceDate = '2017-05-13'