1

I have a ETL cleanup project where I have a unioned date column that includes formats such as "2014-10-14" and "10/14/2014 12:00:00 AM". I am trying to find a slick way I can convert them all using a str_to_date function either in a case statement or some other way to help determine which format it is and then convert it to a date.

So far all my efforts have failed. Any thoughts?

juergen d
  • 201,996
  • 37
  • 293
  • 362
Eric Petersen
  • 63
  • 1
  • 6

1 Answers1

3

str_to_date will return NULL if the particular conversion fails, so you can try doing one first. If the result is NULL, try the other format.

For example:

coalesce(
  str_to_date(create_date, '%Y-%m-%d'),
  str_to_date(create_date, '%m/%d/%Y %h:%i:%s %p'),
  str_to_date(create_date, '%d/%b/%Y %H:%i:%s')
)
Samuel Bolduc
  • 18,163
  • 7
  • 34
  • 55
Justin Kiang
  • 1,270
  • 6
  • 13