CODEWITHSUNDEEP
sqlgoogle-bigquery
user11035754
user11035754

Reputation: 227

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

Upvotes: 0

Views: 271

Answers (3)

maleterian
maleterian

Reputation: 188

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

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

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

Upvotes: 2

GaelPC1123
GaelPC1123

Reputation: 66

Command

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

should work!

Upvotes: 0

Related Questions