Thom A - Friday, October 20, 2017 12:21 PM

I'm surprised you've said that string like '18th February 2015' are being returned with the LIKE statement (as they wouldn't).

DesNorton - Friday, October 20, 2017 11:58 AM

declare @datestring varchar(20) = '19th May 2015';

select convert(date, replace(@datestring, 'th', ''));

The problem with this, Des, (edit and now Lynn who has posted the same) is that if the OP has data with "10th" they probably have data with "1st", "21st" and even "31st". That presents a problem as...

REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','st ',' '),'nd ',' '),'rd ',' '),'th ',' ')
Returns '1 Augu 2017'. You're probably going to need to do something more like...
REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','1st','1'),'nd ',' '),'rd ',' '),'th ',' ')

I know I've been shot down about it before, but I'm going to say it again; Get the provider to fix their data... There's far far far too many permutations. I woulnd't be surprised if we start seeing examples of dates such as 'Friday, 20th October 2017' soon, or (even worse), something stupid like 'Thursday, 11st October 2017' (2017-10-11 was a Wednesday 😉 ).