How to split single column value in SQL server

If I understand the question correctly, you want to have the values on separate rows.

To split the values there are quite a few ways to do it, some mentioned in previous solutions. If the date format contains leading zeroes for month and day then you can split the data based on the second space.

As an example, consider the following data

CREATE TABle #TheTable (
   TheValue varchar(100)
);
INSERT INTO #TheTable VALUES ('2016-04-05 10.00 2016-04-05 8.00');
INSERT INTO #TheTable VALUES ('2016-04-06 11.00 2016-04-06 9.00');
INSERT INTO #TheTable VALUES ('2016-04-07 12.00 2016-04-07 10.00');

Now you could query the data with following query

WITH SeparatedColumns AS  (
   SELECT ROW_NUMBER() OVER (ORDER BY a.TheValue) AS RowNo,
      SUBSTRING(a.TheValue, 1, CHARINDEX(' ', a.TheValue, 12)) AS StartCol,
      SUBSTRING(a.TheValue, CHARINDEX(' ', a.TheValue, 12), 999) AS EndCol
   FROM #TheTable a
)
SELECT b.RowNo,
       CONVERT(datetime, REPLACE(TRIM(b.StartCol), '.', ':'), 120),
       'In time'
FROM SeparatedColumns b
UNION ALL
SELECT b.RowNo,
       CONVERT(datetime, REPLACE(TRIM(b.EndCol), '.', ':'), 120),
       'Out time'
FROM SeparatedColumns b
ORDER BY 1, 2

In the CTE query the data is split into two columns. An extra column is added in order to see which values come from the same row. Otherwise you wouldn't know what dates should be handled together if the result set contains multiple rows.

In the outer part an UNION expression is used to fetch the columns on separate rows. In the end the data is ordered based on the original row number so that the rows from the same data are near to each other.

So the result would be

RowNo (No column name)        (No column name)
----- -----------------------  -------------------
1     2016-04-05 08:00:00.000  Out time
1     2016-04-05 10:00:00.000  In time
2     2016-04-06 09:00:00.000  Out time
2     2016-04-06 11:00:00.000  In time
3     2016-04-07 10:00:00.000  Out time
3     2016-04-07 12:00:00.000  In time

However, if you have the possibility to modify the schema, I would suggest:
- Don't store two separate data items in the same column. Use separate columns
- Don't store dates in varchar columns, always use proper data type.

If you are using SQL 2012, You can also experiment with it using the PARSENAME function, here is an example:

DECLARE @INTIMEOutime TABLE (Id INT IDENTITY(1,1), SomeDateTime VARCHAR(50))

INSERT INTO @INTIMEOutime
	SELECT '2016-04-05 10.00 2016-04-05 8.00' UNION 
	SELECT '2016-04-05 9.45 2016-05-05 12.10' 

--SELECT REPLACE(REPLACE(@dummy,'.','|'),' ','.')

 SELECT Id,
 REPLACE(
	PARSENAME(REPLACE(REPLACE(SomeDateTime,'.','|'),' ','.'),2) + ' ' + PARSENAME(REPLACE(REPLACE(SomeDateTime,'.','|'),' ','.'),1),
	'|','.') 'OutTime',
 REPLACE(
	PARSENAME(REPLACE(REPLACE(SomeDateTime,'.','|'),' ','.'),4) + ' ' +  PARSENAME(REPLACE(REPLACE(SomeDateTime,'.','|'),' ','.'),3),
	'|','.') 'InTime'
FROM @INTIMEOutime

Or, use value() method of the XML, here is an example:

DECLARE @INTIMEOutime TABLE(Id INT IDENTITY,SomeDateTime VARCHAR(8000));
INSERT INTO @INTIMEOutime(SomeDateTime)VALUES('2016-04-05 10.00 2016-04-05 8.00');
INSERT INTO @INTIMEOutime(SomeDateTime)VALUES('2016-04-05 9.45 2016-05-05 12.10');

SELECT
    Id,
    InTime=n.v.value('/e[1]','VARCHAR(16)') + ' ' + n.v.value('/e[2]','VARCHAR(16)'),
     OutTime=n.v.value('/e[3]','VARCHAR(16)') + ' ' + n.v.value('/e[4]','VARCHAR(16)')
FROM
    @INTIMEOutime
    CROSS APPLY (
        SELECT 
            CAST('<e>'+REPLACE(SomeDateTime,' ','</e><e>')+'</e>' AS XML) AS itm
    ) AS i
    CROSS APPLY i.itm.nodes('/') AS n(v);

Both of them will output:

Id	InTime	                OutTime
1	2016-04-05 10.00	2016-04-05 8.00
2	2016-04-05 9.45	        2016-05-05 12.10

SQL Server Helper - Tip of the Day[^]

sql - Split Column with delimiter into multiple columns - Stack Overflow[^]