Skip to content
Advertisement

Get DateTime from text

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
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement