separate the check-in and check-out form a column datetime data type msssql

Question

I need to separate the checkintime column as AM as checkin and PM as checkout;

enter image description here

kindly view the image here http://postimg.org/image/zc3r0ba71/ @Rahul Dambare — Manikandan G, Jan 29 '16 at 05:12
kindy view the image form the link http://postimg.org/image/zc3r0ba71/ — Manikandan G, Jan 29 '16 at 05:13
Possible duplicate of [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Rahul, Jan 29 '16 at 05:18

score 0 · Answer 1 · edited May 23 '17 at 11:53

0

Try this query .. in like clause add your search criteria.

SELECT CASE
         WHEN checktime LIKE '%AM%' THEN LEFT(checktime, Charindex(' ', checktime) - 1)
         ELSE name
       END,
       CASE
         WHEN checktime LIKE '%PM%' THEN RIGHT(checktime, Charindex(' ', Reverse(checktime)) - 1)
       END
FROM   YourTable

For more check here

Or try this solution -

select ParsedData.* 
from MyTable mt
cross apply ( select str = mt.String + ',,' ) f1
cross apply ( select p1 = charindex( ',', str ) ) ap1
cross apply ( select p2 = charindex( ',', str, p1 + 1 ) ) ap2
cross apply ( select Nmame = substring( str, 1, p1-1 )                   
                 , Surname = substring( str, p1+1, p2-p1-1 )
          ) ParsedData

For more info click here

edited May 23 '17 at 11:53

Community

1
1

answered Jan 29 '16 at 05:18

Rahul

763
1
12
45

NOT YET WORKING , kindly see this picture to http://s7.postimg.org/p4dl4swpn/Untitled.jpg my code is : SELECT CHECKINOUT.USERID, Ent_Staff.StaffName,CHECKINOUT.CHECKTIME FROM Ent_Staff INNER JOIN USERINFO ON USERINFO.BADGENUMBER=Ent_Staff.Bio_Id INNER JOIN Mas_Shift ON Mas_Shift.Shift_Id=Ent_Staff.Shift_Id INNER JOIN checkinout ON checkinout.USERID=USERINFO.USERID WHERE checkinout.CHECKTIME >= '01/27/2016' AND checkinout.CHECKTIME < '01/28/2016 ' AND CHECKINOUT.USERID IN (9,15) ORDER BY checkinout.CHECKTIME @Rahul Dambare – Manikandan G Jan 29 '16 at 05:45

score 0 · Answer 2 · answered May 12 '16 at 07:06

I have developed an Attendance system few days ago. Most of the time attendance machines just save a log for your entry. If an employee punches 5 times there will be 5 entry in that day for that employee. What I did I separated all the entries for a single day of an employee and took the first and last entry for check-in and check-out. Querying with AM/PM is not a good idea, both check-in or check-out can be AM or PM.

separate the check-in and check-out form a column datetime data type msssql

2 Answers2