Hi,

Thanks for all your replies. Here is the query worked as required.

select t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,

(select SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,min(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 18, 2) from view1 st

where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='1'

)as InTime,

(select SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,max(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 18, 2) from view1 st

where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='2'

)as OutTime

from view1 t where t.cardno in ('111111','222222') and group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)