MySQL STR_TO_DATE() Function

The MySQL STR_TO_DATE() function takes a string and returns a date or datetime value specified by a format mask. Based upon the arguments, this function returns the following:

• Returns a datetime value, if the string contains both valid date and time parts.
• Returns a date value, if the string contains only valid date parts.
• Returns a time value, if the string contains only valid time parts.
• Returns a NULL value, if it is unable to extract valid date and time parts using the format_mask.

Syntax

STR_TO_DATE(string, format_mask)

Parameters

Return Value

Returns the time or date or datetime value as specified by a format mask.

Example 1:

The example below shows the usage of STR_TO_DATE() function.

mysql> SELECT STR_TO_DATE('August 18 2018', '%M %d %Y');
Result: '2018-08-18'

mysql> SELECT STR_TO_DATE('August 18, 2018', '%M %d, %Y');
Result: '2018-08-18'

mysql> SELECT STR_TO_DATE('Saturday, August 18, 2018', '%W, %M %d, %Y');
Result: '2018-08-18'

mysql> SELECT STR_TO_DATE('08,18,2018', '%m,%d,%Y');
Result: '2018-08-18'

mysql> SELECT STR_TO_DATE('8,18,2018,10,30', '%m,%d,%Y,%h,%i');
Result: '2018-08-18 10:30:00'

mysql> SELECT STR_TO_DATE('10,30,45', '%h,%i,%s');
Result: '10:30:45'

mysql> SELECT STR_TO_DATE('August1234 18, 2018', '%M%# %d, %Y');
Result: '2018-08-18'

mysql> SELECT STR_TO_DATE('August! 18, 2018', '%M%. %d, %Y');
Result: '2018-08-18'

Example 2:

Consider a database table called Employee with the following records:

In the query below, the STR_TO_DATE() function is used to format the string value of Date_of_Joining column:

SELECT *, 
STR_TO_DATE(Date_of_Joining, '%M %d, %Y') AS STR_TO_DATE_Value
FROM Employee;

This will produce a result similar to: