Click here to Skip to main content
14,694,039 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
INTIMEOutime

2016-04-05 10.00 2016-04-05 8.00

from the above column i want to split the value intime and out time separtely

i want the output as follows

2016-04-05 10.03 intime

2016-04-05 8.00 Outtime


for that how to do in sql server

What I have tried:

INTIMEOutime

2016-04-05 10.00 2016-04-05 8.00

from the above column i want to split the value intime and out time separtely

i want the output as follows

2016-04-05 10.03 intime

2016-04-05 8.00 Outtime


for that how to do in sql server
Posted
Updated 9-Apr-18 17:59pm

Here is a simple solution: Split string in Sql server 2008[^]

Also see user defined function here: SQL User Defined Function to Parse a Delimited String[^]

If you have SQL Server 2016 you can use String_Split()
   
v2
Comments
Maciej Los 10-Apr-18 2:06am
   
;)
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.
   
v2
Comments
CHill60 10-Apr-18 4:02am
   
5'd for the sound advice at the end!
Wendelius 11-Apr-18 11:42am
   
Thank you very much!
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[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900