Extract date from string and insert into field Microsoft SQL Server 2012

Question

Say I have a field UserAddedInfo with a string "User was added to the system and removed from list on 16/05/2016 by User Annon" and a field DateAdded in the same table.

Is there any way in SQL to extract that 16/05/2016 date from the string field and insert it into the DateAdded field as a datetime?

The date in the string is always going to be dd/MM/yyyy.

Thanks!

Answer 1

Use PATINDEX to get the start position of the date string in the column and extract 10 characters from that position. To convert the extracted string to date, use CONVERT with format 103.

103 = dd/mm/yyyy

select 
convert(date,
substring(UserAddedInfo,patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',UserAddedInfo),10)
      ,103)
from table_name
where patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',UserAddedInfo) > 0

To update the dateadded field in the table, use

update table_name
set dateadded = convert(date,
substring(UserAddedInfo,patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',UserAddedInfo),10)
      ,103)
where patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',UserAddedInfo) > 0

Use try_cast or try_convert to return null when the substring returns invalid dates.

select 
try_cast(
substring(UserAddedInfo,patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',UserAddedInfo),10) 
as date) 
--or
--try_convert(date,
--substring(UserAddedInfo,patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',UserAddedInfo),10) 
--) 
from table_name
where patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',UserAddedInfo) > 0