Getting date from convoluted text field in SQL Server-CodePudding

I need to extract the date/time from a text field that looks like this, into a date/time column:

some text - 29th Jul 2021 16:44

some different text - 2nd Jul 2021 12:31

Example code to reproduce:

select 'some text - 29th Jul 2021 16:44' 
union
select
'some different text - 2nd Jul 2021 12:31'
as textfield

This is a vendor supplied database I'm querying - there's no option to change the format.

I need to extract the date & time into a datetime field (the purpose is to do a comparison to a different date time field).

Is there any 'shortcuts' to doing this? I've began attempting lots of manual substring functions to extract individual parts to piece back together again, but its very cumbersome, and I feel like there must be a better way.

The dash (-) is always going to be in the same position (relative to the date aspects), which has been helpful, but I still feel like I'm going down the wrong approach.

Is there a way I can substring after the dash, and for SQL Server to recognise the format?

A challenge here is the 'day' aspect will be single digit for 1-9, but double digit for 10-31.

CodePudding user response：

For your sample data you can do this with a few uses of replace and a substring:

with t as (
    select 'some text - 29th Jul 2021 16:44' as textfield
    union
    select
    'some different text - 2nd Jul 2021 12:31'
)
select Try_parse(y.d as datetime using 'en-GB') as ExtractedDate
from t
cross apply(values(Substring(t.textfield, CharIndex('-',t.textfield) 2 ,100)))x(v)
cross apply(values(Replace(Replace(Replace(Replace(x.v,'st',''),'nd',''),'rd',''),'th','')))y(d)

CodePudding user response：

If you like concise :

convert(datetime, stuff(right(txt, 19), 3, 2, ''), 106)

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=c8720885ef6239187b2c220d0dfa9ae2

This conversion relies on there being a space following the hyphen so that when picking up the rightmost 19 characters you'll either end up with a digit or a space character in the initial position. This then allows you to strip out the two characters of ordinal text from a known location. (I had initially put an ltrim() in before the conversion but the possibly leading space doesn't seem to break the conversion anyway.)

One advantage is that is avoids the potential of having other hyphens in the lead portion of the text interfering with the search. The whole issue of a marker/seperator is eliminated completely.