retrieve different format of date values with time from string (t-sql)

Question

I have a requirement where i have to pull the date/time value from string but the problem is that they can be different formats because of which substring becomes more complicated.

Here's what i came up with but is there any other method where i could simply retreive dates of different format with time and convert them all in single format?

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (
    comments varchar(500)
   
)

insert into #temp (comments)
(
 select 'Mailed on 1/1/22 at 5 pm'
 union
 select 'Mailed on 01/2/2222 @ 6 am'
 union
 select 'Mailed on 01/2/22 in night'
 union
 select 'Mailed on 1/02/2222 at 4 pm'
 union
 select 'Mailed on 1/1/2222 at 4 pm'

);

select *
from #temp

cross apply (select PATINDEX('%Mailed On%',comments) as start_pos) as start_pos
cross apply (select case when substring(comments,patindex('%Mailed On%',comments)+9,11) like '%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' then 1 
                         when substring(comments,patindex('%Mailed On%',comments)+9,8)  like '%[0-9][0-9]/[0-9]/[0-9][0-9]%' then 2
                         when substring(comments,patindex('%Mailed On%',comments)+9,10) like '%[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' then 3
                         when substring(comments,patindex('%Mailed On%',comments)+9,9)  like '%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%' then 4
                         when substring(comments,patindex('%Mailed On%',comments)+9,9)  like '%[0-9]/[0-9]/[0-9][0-9][0-9][0-9]%' then 5
                         when substring(comments,patindex('%Mailed On%',comments)+9,7)  like '%[0-9]/[0-9]/[0-9][0-9]%' then 6 else null end as substr) as substr
--cross apply (select case when substring(authcomments,start_pos + 9, 11) like '%[1-9]/[0123][0-9]/[0-9][0-9][0-9][0-9]%' then 1 else null end as substr)  as substr
cross apply (select case when substr = 1 then substring(comments,patindex('%Mailed On%',comments)+9,11) 
                         when substr = 2 then substring(comments,patindex('%Mailed On%',comments)+9,8)
                         when substr = 3 then substring(comments,patindex('%Mailed On%',comments)+9,10)
                         when substr = 4 then substring(comments,patindex('%Mailed On%',comments)+9,9)
                         when substr = 5 then substring(comments,patindex('%Mailed On%',comments)+9,9)
                         when substr = 6 then substring(comments,patindex('%Mailed On%',comments)+9,7)
                         else null end as maileddate
            )  as maileddate 

Answer 1

@user1672315 ,

Sometimes you get stuff like this and in order to fix it so that you can get the dates and times to store in a table or whatever, ya gotta do what ya gotta do to get it and, contrary to the comments, it certainly CAN be done in SQL. It's just not that difficult. Ya just gotta know some of the "gazintas" ;)

So, using the readily consumable test data that you were nice enough to provide, run the following code against it...

 SELECT t.*
        ,TheDateAndTime = DATEADD(hh,ca4.cHour,ca3.cDate)
   FROM #temp t
  CROSS APPLY(VALUES(SUBSTRING(comments,PATINDEX('%[0-9]%',comments),500)))                   ca1(DT)
  CROSS APPLY(VALUES(SUBSTRING(ca1.dt,PATINDEX('% [0-9]%',ca1.dt),500)))                      ca2(TM)
  CROSS APPLY(VALUES(TRY_CONVERT(DATETIME,SUBSTRING(ca1.DT,1,PATINDEX('%[0-9] %',ca1.DT)))))  ca3(cDate)
  CROSS APPLY(VALUES(IIF(ca2.TM LIKE '%night%',23,DATEPART(hh,TRY_CONVERT(DATETIME,ca2.TM)))))ca4(cHour)
;

... and see that you CAN do it in SQL... BUT, see the warnings below the graphic below.

You also need to figure out what hour "night" is going to be assigned. I assigned "23" as the hour.

Results are as follows:

I'm thinking that your "2222" years are in error, though. :D

One thing I do agree on is that the format needs to be somewhat consistent. No code in the world, Python or otherwise, will be able to distinguish between a mm-dd-yy and dd-mm-yy format when dd and mm are both less than 13. The code I posted assumes (m)m-(d)d-yy and is based on the current LANGUAGE and DATEFORMAT that I'm using. It WILL return NULLs where the mm part isn't between 1 and 12 or if the dd part isn't between 1 and 31 or if the date is an "illegal date" like 2/29/2021, etc, though.

It also assumes that the format will always contain the numeric date as the first set of numeric values it comes across and that the time will always be the last thing in the string. We can add more checks, if needed but, like I said, unless mm is >=13, it cannot (nor can anything else) determine if it should be mm-dd-yy or dd-mm-yy because there's simply no other information in the string to indicate which format is being used. You MUST check your date format to use this, as well. If the strings are supposed to be in the dd-mm-yy format, we may have to make a change (although I believe SQL server will auto-magically accommodate that if the DATEFORMAT matches the intention of the string).