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
Advertisement
Answer
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