In the WHERE clause, you shouldn't use single quotes for your datetime parameters.
Try this:
Create proc sp_backupManpowerCollection
@ATTFromDate datetime,
@ATTToDate datetime,
@Location nvarchar(50)
as
INSERT INTO tblBacManpoweCollection
SELECT EmpCode, ATTdate, LineCode,LineName,DesigCode,Designation,CompanyName,UnitName,DeptName,SecName,InHour,
InMin,OutHour,OutMin,Location,Counter,TotalOTHour,DayFlag,ProcessTime FROM tblManpowerCollection
WHERE ATTdate between @ATTFromDate and @ATTToDate and Location='@Location'
delete tblManpowerCollection where ATTdate between @ATTFromDate and @ATTToDate and Location=@Location