Get DateTime from from a text in SQL Server-CodePudding

How to separate and display only the DateTime from the string?

I have a column that holds this data:

Comments
-----------------------------------------
Daniel reached the school by 9:25:00 alone
Breakfast was served by 10:17:27 in the morning
Reached back home late evening by 7:00:48

I used this query to separate out the time:

(CONVERT(varchar(max), Comments, 108)) [Comments],

and it returned the same lines. String didn't get filtered

When I tried using this statement:

(CONVERT(TIME, Comments, 108)) [Comments],

I got this error

Conversion failed when converting date and/or time from character string.

How can I overcome time and get the below expected result?

Comments
----------------------------------------
9:25:00 
10:17:27 
7:00:48

CodePudding user response：

With patindex you can locate the time pattern.

select
try_cast(substring(Comments, patindex('%[ 0-9][0-9]:[0-9][0-9]:[0-9][0-9]%', Comments), 8) as time(0)) CommentTime
from your_table

CommentTime

09:25:00

10:17:27

07:00:48

CommentTime
09:25:00
10:17:27
07:00:48

CodePudding user response：

You can use STRING_SPLIT and TRY_CONVERT.

SELECT Tbl.Comments, T.value
FROM Tbl CROSS APPLY STRING_SPLIT(Tbl.Comments,' ') As T
WHERE TRY_CONVERT(TIME, value) IS NOT NULL

for versions that do not support STRING_SPLIT, the same logic can be implemented using XML, since there is already a solution using PATINDEX.

WITH cte AS (
SELECT Comments, CONVERT(XML, CONCAT('<n>',REPLACE(Comments,' ','</n><n>'),'</n>')) AS x
FROM Tbl)
SELECT cte.Comments, v.value('.','varchar(MAX)')
FROM cte CROSS APPLY x.nodes('/n') AS t(v)
WHERE TRY_CONVERT(TIME, v.value('.','varchar(MAX)')) IS NOT NULL

CodePudding user response：

ltrim(substring(comments, charindex(comments, ':') - 2, 8))

Obviously this assumes there's always a colon and that the first colon is within the time element. It also assumes that all times have a leading space and are 7-8 characters in length. Depending on what you intend to do with this the ltrim() is probably unnecessary.

This is just string parsing. I realize this doesn't actually convert to a time value since it's not clear OP requires that in the output.