Convert character string into this specific date format?

I am using SQL Server 2014 and I have a table (t1) which contain a column (ReviewDate) in the nvarchar format.

An example of a row of this column is given below:

       ReviewDate
  Mr John wrote a review in Oct 2017

I need to extract the “date” component from this character string.

To do this, my T-SQL is as follows:

SELECT (RIGHT([ReviewDate], 8)) as [ReviewDate 2]
FROM t1

This gives me “Oct 2017”.

Now, I want to convert the “Oct 2017” into “2017-10-01” as a datetime format. This is where I am stuck.

I have tried the following:

SELECT CONVERT(datetime, (RIGHT([ReviewDate], 8)), 121) as [ReviewDate2]

Above syntax gives me the following error message: “Conversion failed when converting date and/or time from character string.”

SELECT CAST( (RIGHT([ReviewDate], 8)) as datetime) as [ReviewDate2]

Above syntax gives me the same error message:

Conversion failed when converting date and/or time from character string.

Some help will be appreciated.

Answer

so far your sample text is a valid datetime in mssql when I tried to cast. It seems there’s some invalid data on your table. try using try_cast() to include those invalid data.

declare @ReviewDate varchar(max)='Mr John wrote a review in Oct 2017'

set  @ReviewDate = (RIGHT(@ReviewDate, 8))

select try_cast(@ReviewDate as datetime) as [ReviewDate2]

dbfiddle<>