I have imported a biometric attendance file(Excel) to sqlserver using c#. In this the time field contains multiple values. So the first value must be extracted as In time and the last value must be out time.
Plz anyone help.
structure of excel sheet is:
SrNo|Emp Code|Emp Name|Time| |Date
1 |0111 |aaa |9.00 9.00 9.00 6.15 6.15 |12-feb-2016
i want to extract first value from column Time i.e 9.00 as In time and last value i.e 6.15 as last time.
What I have tried:
I have tried following query:
"select SUBSTRING([Time],0,CHARINDEX(' ',[Time],0))[In Time],
SUBSTRING([Time],CHARINDEX(' ',[Time],0)+1,LEN([Time]),[Out Time]"
this query on sucess able to separate the the first value as in time but all remaining as out time . so for out time I need last value