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

Question

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

P粉436410586 · Answer

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.