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

​x
 
Comments-----------------------------------------Daniel reached the school by 9:25:00 aloneBreakfast was served by 10:17:27 in the morningReached back home late evening by 7:00:48​

I used this query to separate out the time:

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

 
(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],

 
(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

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

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

 
selecttry_cast(substring(Comments, patindex('%[ 0-9][0-9]:[0-9][0-9]:[0-9][0-9]%', Comments), 8) as time(0)) CommentTimefrom your_table​

CommentTime

09:25:00

10:17:27

07:00:48

Advertisement

Answer