Parsing Custom Date Format in MySQL

Question

Problem

I need to query out a date value for use in some ETL processing. However, the data is stored in MySQL as a VARCHAR column in values like 1/1/19.

What I've tried

A simple CAST(myDateColumn as DATE) fails as I get values like 0001-01-19 returned back.

Question

Am I able to pass a custom date format string into the CAST call somehow to tell it how to parse out the date parts? If not, could a SUBSTRING type of function help here?

Dataset isn't mine to fix, unfortunately. @BillKarwin That STR_TO_DATE() looks like the ticket! — Nick Heidke
– Nick Heidke, Commented Apr 27, 2019 at 17:38

Bill Karwin · Accepted Answer · 2019-04-27 17:40:33Z

2

As discussed in the comments above, you can use the STR_TO_DATE() function to parse a string into a proper YYYY-MM-DD date.

answered Apr 27, 2019 at 17:40

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Heidke Over a year ago

For this example specifically, if someone needs it, it ended up being STR_TO_DATE(myDateColumn, '%m/%d/%Y').

Collectives™ on Stack Overflow

Parsing Custom Date Format in MySQL

Problem

What I've tried

Question

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem

What I've tried

Question

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related