Select a date in a string and convert it to datetime

Question

I have a string like:

'SPY US 03/20/20 P45'

I want to select just the date from the string.

My current query is:

Select Ticker, SUBSTRING(Ticker, PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%',o.Ticker),8) AS 'myDate' FROM TABLE

This returns: 'SPY US 03/20/20 P45', 03/20/20

I want myDate to be in datetime.

I have tried various morphs of cast and convert, but they fail, presumably because they want the format to be in YYYY-MM-DD rather than MM/DD/YY.

My "smartest" attempt to convert was this:

CONVERT(DATETIME, SUBSTRING(o.Ticker, PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%',o.Ticker),8),1)

After reading style guidelines here: https://www.w3schools.com/sql/func_sqlserver_convert.asp but it still failed.

The ideal end-format for the date would be YYYY-MM-DD

Edited to add:

I have been fiddling with it and realized that I over simplied my question. The convert works if I just test it on a string, but the entire query involves several joins.

Answer 1

As I can understand you are looking for something like this.

You can use string_split() function to split string with blank space and then use try_cast() function to check each value whether it is a date.

declare @string as varchar(120) = 'SPY US 03/20/20 P4'
; with cte as (select 
  value
from string_split (@string, ' ')
)Select value from cte where try_cast (value as datetime) is not null

Live db<>fiddle demo.

Answer 2

So it turns out that there were a few entries in the column that didn't have friendly date format, so the patindex clause was returning nonsense.

For some reason that caused the entire operation to fail(rather than just returning null on the few entries that were failing).

Once I selected the entire (ultimately more complicated join statement) into a temp table, then I was able to try_convert the substring into a date and run my operations.