how to solve this issue?

See more:

SQL

hi,

calculating employee lateness using the sp as below

ALTER PROCEDURE [dbo].[spGetEmployeeLateness] --spGetEmployeeLateness '03/01/2014'
@Date as varchar(20)
AS
BEGIN
	select SempAtndSempMstrId,(SempMstrFirstName+' '+isnull(SempMstrMiddleName,'')+' '+
	isnull(SempMstrLastName,'')) as 'EmpName',SdeptDepartmentName,SempAtndTimeIn,SshftInTime,
	
	cast(((datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),SempAtndTimeIn))
    - (datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),
	CAST(CONVERT(VARCHAR,@Date,101)+' '+SshftInTime AS DATETIME))))/(60*60) as varchar)+':'+	

   cast((((datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),SempAtndTimeIn))
   - (datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),
	CAST(CONVERT(VARCHAR,@Date,101)+' '+SshftInTime AS DATETIME))))%(60*60))/60 as varchar)+':'+	
	
	cast((((datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),SempAtndTimeIn))
   - (datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),
	CAST(CONVERT(VARCHAR,@Date,101)+' '+SshftInTime AS DATETIME))))%(60*60))%60 as varchar) as Lateby
		
	
	from SgEmployeeAttendance
	inner join dbo.SgEmployeeMaster on SempMstrId=SempAtndSempMstrId
	inner join SgShiftMaster on SempMstrSshftId=SshftId inner join SgDepartment on SempMstrSdeptId=SdeptId
	inner join SgDesignation on SempMstrSdesgId=SdesgId
	where  cast(convert(varchar(10),SempAtndTimeIn,101)as datetime)=cast(convert(varchar(10),@Date,101) as datetime)
	
	--and  cast(convert(varchar(10),SempAtndTimeIn,101)as datetime)<= DATEADD(day,1,@Date)
	and SempAtndDuty is null and SempMstrId!=1 
	
	group by SempAtndSempMstrId,SempMstrFirstName,SdesgDesignation,SdeptDepartmentName,SempMstrMiddleName,SempMstrLastName,SshftInTime,SempAtndTimeIn
	having min(cast(SempAtndTimeIn as time))>(cast(SshftInTime as time)) order by EmpName
END

but, if an employee shift on 03/01/2014 at 11:30 pm and if he will
come on 03/02/2014 at 12:05:00 am , then how to calculate lateness?

the above sp calucate lateness only on a date base.

thanks..:(