Skip to content
Advertisement

Extract date from a column

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

Advertisement

Answer

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

enter image description here

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

enter image description here

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement