The title is rewritten as: The Chinese translation of converting string to date is MYSQL function STR_TO_DATE

Question

I have this date within this date: STR_TO_DATE('28-AUG-60','%d-%M-%y') When I insert into the table, what I get is: 2060-08-28 00:00:00 What I expect is: 1960-08-28 00:00:00 I know there is a RR format in MSSQL which gives you a 2 or 4 digit year, 00-49 is the 20th century, how do I do that in MySQL? When I looked at the documentation I saw that it should use %Y but it doesn't actually work Thanks!

Answer 1

If you knew that all two-digit years greater than a certain value, say '50', should be considered years in the 1900s, and the rest should be considered years in the 2000s, you Different date formats can be applied using CASE expressions:

SELECT DATE(STR_TO_DATE(
         date,
         CASE WHEN RIGHT(date, 2) < ? THEN '%d-%M-%Y' ELSE '%d-%M-%y' END 
       )) date
FROM tablename;

Change ? to a two-digit year that fits your data (such as '50').

See Demo.