Bad Dub Bad Dub - 4 months ago 24
SQL Question

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

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!

vkp vkp
Answer

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 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) 
from table_name
where patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',UserAddedInfo) > 0