How to extract date from a string like '2:24 PM 6-20-2021'?

For example, I have a column called Dates (datatype 'String') in the table like below -

Dates
2:24 PM 6-20-2021
10:24 PM 6-21-2021

The output should be

Dates
6-20-2021
6-21-2021

CodePudding user response：

It is a 3rd element separated by space

select regrexp_substr ('10:24 PM 6-21-2021', '[^ ] ',1 ,3 ) from dual

explanation :

1. [^ ] not space characters
2. one or more elements(not spaces as it is situated after [^ ])
3. 1 starting from first position
4. 3 is occurence

CodePudding user response：

Command

"select* from table where column == 'date' or column like %'date'%"

should work!

CodePudding user response：

How to extract date from a string?

If you want to extract date as a date type so you will be able then to use data functions - consider below

select dates, 
  date(parse_datetime('%H:%M %p %m-%d-%Y', dates)) date
from your_table             

if applied to sample data in your question - output is

Meantime, if you want to extract data as a string (as it is in original string) - consider below

select dates, 
  split(dates, ' ')[offset(2)] date
from your_table  

with output