Venkat Govind

Venkat Govind

  • 1.2k
  • 577
  • 68.2k

How To avoid this Operand data type date is invalid for sub

Oct 27 2015 5:34 AM
How To avoid this 
 
Msg 8117, Level 16, State 1, Line 6
Operand data type date is invalid for subtract operator.
I am using below Query
 
 
select a.FirstName,a.WorkforceId,c.* from mstr_workforce a left outer join (select distinct(CONVERT(varchar,AttendanceDate,101))as attdate,a.WorkforceID,
(select min(convert(varchar,AttendanceDate,108))from workforce_attendance where WorkforceId=a.WorkforceId and
CONVERT(varchar,AttendanceDate,101)=CONVERT(varchar,AttendanceDate,101)) as InTime, (select max(convert(varchar,AttendanceDate,108))
from workforce_attendance where WorkforceId=a.WorkforceId and
CONVERT(varchar,AttendanceDate,101)=(CONVERT(varchar,a.AttendanceDate,101)))as OutTime,
(select (CONVERT(varchar,max(AttendanceDate)-min(AttendanceDate),108)) from workforce_attendance
where WorkforceId=a.WorkforceId and CONVERT(varchar,AttendanceDate,101)=CONVERT(varchar,a.AttendanceDate,101))
as Work,(select case when convert(varchar,max(AttendanceDate)-min(AttendanceDate),108)< '08:30:00' then '0:00:00'
else convert(varchar,(max(AttendanceDate)-min(AttendanceDate)-'08:30:00'),108) end from workforce_attendance where WorkforceId =a.WorkforceId
and Convert(varchar,AttendanceDate,101)= Convert(varchar,a.AttendanceDate,101)) as OT
,isnull((select case when convert(varchar,max(AttendanceDate),108) = 'NULL' then 'ABS'
else 'PRS' end from workforce_attendance where WorkforceId =a.WorkforceId and Convert(varchar,AttendanceDate,101)=
Convert(varchar,a.AttendanceDate,101)),'ABS') as Remark from workforce_attendance a inner join mstr_workforce b on b.WorkforceId=a.WorkforceId
where AttendanceDate BETWEEN '10/01/2015' AND '10/31/2015' )c on a.WorkforceId=c.WorkforceId
 

Answers (1)