Home  >  Q&A  >  body text

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

<p>I have this date within this date: <code>STR_TO_DATE('28-AUG-60','%d-%M-%y')</code> When I insert into the table, what I get is: <strong>2060-08-28 00:00:00</strong> What I expect is: <strong>1960-08-28 00:00:00</strong> 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! </p>
P粉270891688P粉270891688381 days ago407

reply all(1)I'll reply

  • P粉436410586

    P粉4364105862023-09-03 09:07:18

    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.

    reply
    0
  • Cancelreply