SQL Converting strings to date

this has really baffled me for a few days and am seeking help. I am using SQL Server 2017.

I am trying to handle strings from a free format field and either convert them to a date in the format of "dd/mm/yyyy" or if they are not in this format then simply display the text verbatim.

I need this in a VIEW so can not use SET LANGUAGE. Sounds simple using convert and isdate but does not seem to work.

So for the snippet of code below (remember this will be in a VIEW) I want to read the text and if the string converts to a date (ie. is in the format of "dd/mm/yyyy" then run the convert to a date as I need it in date format for Excel to pick up (via Connect SQL Sever Database)), and if it does not convert to a date then display the text as it is.

create table dateTest1 
( idx int, 
 dateStringTest varchar(15) 
); 
  
insert into dateTest1 (idx, dateStringTest) 
values (1, '13/01/2021'), (2, 'no'); 
 
 
select 
       case when isdate(convert(datetime, dateStringTest, 103)) = 1 
       then convert(datetime, dateStringTest, 103)
       else dateStringTest 
       end as dtres
from dateTest1 
--where idx = 1 
  
-- error: Msg 241, Level 16, State 1, Line 15 Conversion failed when converting date and/or time from character string. 
-- this error happens for idx = 2. Idx = 1 works ok

Any assistance with this would be greatly appreciated as it's doing my head in.

Thanks in advance Paul

I'm not sure how well suited your database is to doing this. The data conversion functions in SQL Server are a bit "clumsy", compared to some others.

However, from your database's point of view:

If something is supposed to be a Date, then it should be a date.
It might be "missing" (i.e. nullable) but it can't be anything else.
If it were to be anything else, then it should not be treated as a Date.

Users cannot enter Dates - only the Character Representations of them.
Displaying [numbers and] dates from your application requires formatting on the "way out".
Reading [numbers and] dates into your application requires the reverse, "un-formatting" (and validation, of course) on the "way in".
Data should always be stored in the correct Data Type - "The Right Tool for the Right Job".

Free-text parsing is probably best done in your application, extracting the date value, validating and "un-formatting" it and then storing it.