MySQL STR_TO_DATE() Function

 The STR_TO_DATE() function in MySQL is used to convert a string into a date or time value based on a given format. This is helpful when you're working with string representations of dates or times and want to convert them into proper DATE, DATETIME, or TIME values that MySQL can use for further operations, such as comparisons or calculations.

Syntax:

STR_TO_DATE(string, format)

• string: The string that contains the date or time in a specific format.
• format: The format in which the string is provided, using various specifiers to match the structure of the date/time in the string.

Format Specifiers:

Here are some commonly used format specifiers:

• %Y – Year (4 digits)
• %y – Year (2 digits)
• %m – Month (2 digits)
• %d – Day of the month (2 digits)
• %H – Hour (00-23)
• %i – Minutes (00-59)
• %s – Seconds (00-59)
• %p – AM/PM (for time values)

Example 1: Converting a Date String

If you have a string representing a date like '2024-12-23' and want to convert it into a DATE:

SELECT STR_TO_DATE('2024-12-23', '%Y-%m-%d');

This will return the date 2024-12-23.

Example 2: Converting a Time String

If the string represents a time, like '03:45:30 PM', and you want to convert it into a TIME:

SELECT STR_TO_DATE('03:45:30 PM', '%h:%i:%s %p');

This will return the time 15:45:30.

Example 3: Converting a Custom Date-Time String

If you have a custom format like '23-Dec-2024':

SELECT STR_TO_DATE('23-Dec-2024', '%d-%b-%Y');

This will return the date 2024-12-23.

Notes:

• The STR_TO_DATE() function returns NULL if the string cannot be converted according to the given format.
• The format specifiers are case-sensitive, so make sure to use the correct ones (e.g., %Y for a 4-digit year vs. %y for a 2-digit year).

Let me know if you need help with more examples!