MySQL STR_TO_DATE() Function

  1. Overview
  2. MYSQL
  3. Date Functions
  4. MySQL STR_TO_DATE() Function

The STR_TO_DATE() function in MySQL is used to convert a string into a date using a specified format.

Definition and Usage

  • Purpose: Converts a string into a date or datetime value based on a given format.
    • The string should match the format specified in the format argument.

Syntax

STR_TO_DATE(string, format)

Parameters

  1. string: Required. The string to be formatted into a date or datetime value.
  2. format: Required. A format string that specifies how the date or datetime should be parsed.
    • You can use various format specifiers to define the string's structure.

Format Specifiers

  • %a: Abbreviated weekday name (Sun to Sat)
  • %b: Abbreviated month name (Jan to Dec)
  • %c: Numeric month (1 to 12)
  • %d: Day of the month (01 to 31)
  • %e: Day of the month (0 to 31)
  • %H: Hour (00 to 23)
  • %h: Hour (01 to 12)
  • %i: Minutes (00 to 59)
  • %j: Day of the year (001 to 366)
  • %M: Full month name (January to December)
  • %p: AM or PM
  • %r: Time in 12-hour AM/PM format (hh:mm:ss AM/PM)
  • %S: Seconds (00 to 59)
  • %Y: 4-digit year (e.g., 2024)
  • %y: 2-digit year (e.g., 24)

Technical Details

  • Works in: MySQL 4.0 and later
  • Return Type: DATE, DATETIME, or TIME depending on the input string and format.
  • If the format does not match the string, the function returns NULL.

Examples

Example 1: Converting a Date String with Month Name

SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");

Result: 2017-08-10
Explanation: Converts the string "August 10 2017" into the date 2017-08-10.

Example 2: Converting a Date with Commas

SELECT STR_TO_DATE("August,5,2017", "%M,%e,%Y");

Result: 2017-08-05
Explanation: Converts "August,5,2017" into the date 2017-08-05, considering the commas in the format.

Example 3: Converting a Full Date with Weekday and Month Name

SELECT STR_TO_DATE("Monday, August 14, 2017", "%W %M %e %Y");

Result: 2017-08-14
Explanation: Converts the string with the full weekday and month name into a date.

Example 4: Converting a Date and Time with Specific Format

SELECT STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s");

Result: 2017-08-14 10:40:10
Explanation: Converts the string into a DATETIME value, interpreting commas and spaces as separators.

Use Cases

  • Parsing Dates: When you have dates in a non-standard format (e.g., with month names or custom delimiters), STR_TO_DATE() can be used to convert them to a standard date format.
  • Data Import: If you're importing data where dates are stored as strings in non-standard formats, this function can help parse them into valid DATE or DATETIME values.
  • Flexible Date Handling: For dealing with user input or external data where the date formats vary, STR_TO_DATE() allows you to define how to interpret different date formats.

Key Notes

  • Ensure that the format matches the string structure exactly for successful conversion. If there's a mismatch, the function returns NULL.
  • This function is versatile and supports various date and time formats, making it useful for dealing with different date input scenarios.
Was this article helpful?