Syntax\n------\n\nSTR_TO_DATE(str,format)\n\nDescription\n-----------\n\nThis is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns aDATETIME value if the format string contains both date and time parts, or aDATE or TIME value if the string contains only date or time parts.\n\nThe date, time, or datetime values contained in str should be given in the format indicated by format. If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An illegal value also produces a warning.\n\nUnder specific SQL_MODE settings an error may also be generated if the str isn't a valid date:\n\n ALLOW_INVALID_DATES\n NO_ZERO_DATE\n* NO_ZERO_IN_DATE\n\nThe options that can be used by STR_TO_DATE(), as well as its inverse DATE_FORMAT() and the FROM_UNIXTIME() function, are:\n\n| Option | Description                                                                                                                                                                       |\n| ------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |\n| %a     | Short weekday name in current locale (Variable lc_time_names). |\n| %b     | Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.                                                    |\n| %c     | Month with 1 or 2 digits.                                                                                                                                                         |\n| %D     | Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).                                                                                                            |\n| %d     | Day with 2 digits.                                                                                                                                                                |\n| %e     | Day with 1 or 2 digits.                                                                                                                                                           |\n| %f     | Microseconds 6 digits.                                                                                                                              |\n| %H     | Hour with 2 digits between 00-23.                                                                                                                                                 |\n| %h     | Hour with 2 digits between 01-12.                                                                                                                                                 |\n| %I     | Hour with 2 digits between 01-12.                                                                                                                                                 |\n| %i     | Minute with 2 digits.                                                                                                                                                             |\n| %j     | Day of the year (001-366)                                                                                                                                                         |\n| %k     | Hour with 1 digits between 0-23.                                                                                                                                                  |\n| %l     | Hour with 1 digits between 1-12.                                                                                                                                                  |\n| %M     | Full month name in current locale (Variable lc_time_names).    |\n| %m     | Month with 2 digits.                                                                                                                                                              |\n| %p     | AM/PM according to current locale (Variable lc_time_names).    |\n| %r     | Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.                                                                                                               |\n| %S     | Seconds with 2 digits.                                                                                                                                                            |\n| %s     | Seconds with 2 digits.                                                                                                                                                            |\n| %T     | Time in 24 hour format. Short for '%H:%i:%S'.                                                                                                                                     |\n| %U     | Week number (00-53), when first day of the week is Sunday.                                                                                                                        |\n| %u     | Week number (00-53), when first day of the week is Monday.                                                                                                                        |\n| %V     | Week number (01-53), when first day of the week is Sunday. Used with %X.                                                                                                          |\n| %v     | Week number (01-53), when first day of the week is Monday. Used with %x.                                                                                                          |\n| %W     | Full weekday name in current locale (Variable lc_time_names).  |\n| %w     | Day of the week. 0 = Sunday, 6 = Saturday.                                                                                                                                        |\n| %X     | Year with 4 digits when first day of the week is Sunday. Used with %V.                                                                                                            |\n| %x     | Year with 4 digits when first day of the week is Monday. Used with %v.                                                                                                            |\n| %Y     | Year with 4 digits.                                                                                                                                                               |\n| %y     | Year with 2 digits.                                                                                                                                                               |\n| %#     | For str_to_date(), skip all numbers.                                                                                                                          |\n| %.     | For str_to_date(), skip all punctation characters.                                                                                                            |\n| %@     | For str_to_date(), skip all alpha characters.                                                                                                                 |\n| %%     | A literal % character.                                                                                                                                                            |\n\nExamples\n--------\n\nSELECT STR_TO_DATE('Wednesday, June 2, 2014', '%W, %M %e, %Y');\n+---------------------------------------------------------+\n| STR_TO_DATE('Wednesday, June 2, 2014', '%W, %M %e, %Y') |\n+---------------------------------------------------------+\n| 2014-06-02                                              |\n+---------------------------------------------------------+\n\nSELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y');\n+--------------------------------------------------------------+\n| STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y') |\n+--------------------------------------------------------------+\n| NULL                                                         |\n+--------------------------------------------------------------+\n1 row in set, 1 warning (0.00 sec)\n\nSHOW WARNINGS;\n+---------+------+-----------------------------------------------------------------------------------+\n| Level   | Code | Message                                                                           |\n+---------+------+-----------------------------------------------------------------------------------+\n| Warning | 1411 | Incorrect datetime value: 'Wednesday23423, June 2, 2014' for function str_to_date |\n+---------+------+-----------------------------------------------------------------------------------+\n\nSELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y');\n+----------------------------------------------------------------+\n| STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y') |\n+----------------------------------------------------------------+\n| 2014-06-02                                                     |\n+----------------------------------------------------------------+\n\nURL: https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date