TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
1
)
How to do calculations in grid view based on date
using thumb nail