How to parse string to a datetime value using the STR_TO_DATE() function?

STR_TO_DATE(str, format) is a MySQL built-in function that parses string to a datetime value according to a given format. For example:

SELECT STR_TO_DATE('01,5,2023', '%d,%m,%Y');
  -- +--------------------------------------+
  -- | STR_TO_DATE('01,5,2023', '%d,%m,%Y') |
  -- +--------------------------------------+
  -- | 2023-05-01                           |
  -- +--------------------------------------+

SELECT STR_TO_DATE('May 1, 2023', '%M %d,%Y');
  -- +----------------------------------------+
  -- | STR_TO_DATE('May 1, 2023', '%M %d,%Y') |
  -- +----------------------------------------+
  -- | 2023-05-01                             |
  -- +----------------------------------------+

Extra trailing characters are ignored in the parsing process.

SELECT STR_TO_DATE('01,5,2023XYZ', '%d,%m,%Y');
  -- +-----------------------------------------+
  -- | STR_TO_DATE('01,5,2023XYZ', '%d,%m,%Y') |
  -- +-----------------------------------------+
  -- | 2023-05-01                              |
  -- +-----------------------------------------+

Parsing a string with extra leading characters returns NULL.

SELECT STR_TO_DATE('ABC01,5,2023', '%d,%m,%Y');
  -- +-----------------------------------------+
  -- | STR_TO_DATE('ABC01,5,2023', '%d,%m,%Y') |
  -- +-----------------------------------------+
  -- | NULL                                    |
  -- +-----------------------------------------+

If NO_ZERO_DATE in sql_mode is enabled, zero dates are disallowed. Parsing a string with missing date or time components returns NULL.

SHOW VARIABLES LIKE 'sql_mode';
  -- +---------------+----------------------------------------------------------------+
  -- | Variable_name | Value                                                          |
  -- +---------------+----------------------------------------------------------------+
  -- | sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,        |
  -- |               | NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
  -- +---------------+----------------------------------------------------------------+

SELECT STR_TO_DATE('2023', '%Y'), STR_TO_DATE('9', '%s');
  -- +---------------------------+------------------------+
  -- | STR_TO_DATE('2023', '%Y') | STR_TO_DATE('9', '%s') |
  -- +---------------------------+------------------------+
  -- | NULL                      | NULL                   |
  -- +---------------------------+------------------------+

Reference information of the STR_TO_DATE() function:

STR_TO_DATE(str, format): datetime
  Takes a string str and a format string format and returns
  a DATETIME value, if the format string contains both date and time
  parts. It returns a DATE or TIME value if the string contains
  only date or time parts.

Arguments, return value and availability:
  str: Required. The string to be parsed.
  format: Required. The string with format specifiers.
  datetime: Return value. The parsed datetime, date or time value.
  Available since MySQL 4.

Datetime format specifiers:
  Specifier   Description
  ---------   -----------
  %a          Abbreviated weekday name (Sun..Sat)
  %b          Abbreviated month name (Jan..Dec)
  %c          Month, numeric (0..12)
  %D          Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
  %d          Day of the month, numeric (00..31)
  %e          Day of the month, numeric (0..31)
  %f          Microseconds (000000..999999)
  %H          Hour (00..23)
  %h          Hour (01..12)
  %I          Hour (01..12)
  %i          Minutes, numeric (00..59)
  %j          Day of year (001..366)
  %k          Hour (0..23)
  %l          Hour (1..12)
  %M          Month name (January..December)
  %m          Month, numeric (00..12)
  %p          AM or PM
  %r          Time, 12-hour (hh:mm:ss followed by AM or PM)
  %S          Seconds (00..59)
  %s          Seconds (00..59)
  %T          Time, 24-hour (hh:mm:ss)
  %U          Week (00..53), where Sunday is the first day of the week
  %u          Week (00..53), where Monday is the first day of the week
  %V          Week (01..53), where Sunday is the first day of the week
  %v          Week (01..53), where Monday is the first day of the week
  %W          Weekday name (Sunday..Saturday)
  %w          Day of the week (0=Sunday..6=Saturday)
  %X          Year for the week where Sunday is the first day of the week,
              numeric, four digits; used with %V
  %x          Year for the week where Monday is the first day of the week,
              numeric, four digits; used with %v
  %Y          Year, numeric, four digits
  %y          Year, numeric (two digits)
  %%          A literal % character
  %x          x, for any “x” not listed above

⇒ SUBDATE() - Synonym for DATE_SUB()

⇐ SEC_TO_TIME() - Converting Seconds to Time

⇑ MySQL Functions on Date and Time

⇑⇑ MySQL Function References