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

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra11y ago
Accepted answer
,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

Above code snippet was taken from your query. You are using isnull() and if it is null then value will be "ABS". "ABS" is not a date. And when your is doing any opration with "
"ABS" value it is throwing error. So use a valid date('2011-03-07 ') here.
+1