str_to_date returning incorrect year value

Question

I'm trying to convert a date with m/d/y format to yyyy-mm-dd.

select lastname, firstname, filingdate, str_to_date(filingdate, '%m/%d/%y') as reformattedDate
from Table;

For the returned dates, the month and day are correct, but the year is always 2020, even though the years in the original dates range from 2008 to 2021.

Sample query results

What am I doing wrong?

Answer 1

%y only recognizes 2 digits of year you must use %Y

SELECT  str_to_date('01/01/2017', '%m/%d/%y'),str_to_date('01/01/2017', '%m/%d/%Y')

str_to_date('01/01/2017', '%m/%d/%y') | str_to_date('01/01/2017', '%m/%d/%Y')
:------------------------------------ | :------------------------------------
2020-01-01                            | 2017-01-01                           

db<>fiddle here

Answer 2

Use %Y rather than %y:

select lastname, firstname, filingdate,
       str_to_date(filingdate, '%m/%d/%Y') as reformattedDate

%y is for a two-digit year. %Y is for a four-digit year.