MySQL STR_TO_DATE() Function

MySQL STR_TO_DATE() function; In this tutorial, i am going to show you MySQL str_to_date() function with the help of examples.

MySQL STR_TO_DATE() Function

This function in MySQL helps to convert string values to date or time or DateTime values. The function will return zero (0000-00-00) if an empty string is passed as an argument.

Syntax of MySQL STR_TO_DATE() Function

The syntax of MySQL str_to_date() function; as follows:

STR_TO_DATE(str,format)

Here, str (the first parameter) is the string in which there are date parts, and the format (second parameter) format is the string part (determines how string argument is formatted).

Example 1 – MySQL STR_TO_DATE() Function

Let’s take a first basic example of str_to_date() function; as follows:

SELECT STR_TO_DATE('10,12,2019','%d,%m,%Y');

Output-1

+--------------------------------------+
| STR_TO_DATE('10,12,2019','%d,%m,%Y') |
+--------------------------------------+
| 2019-07-20                           |
+--------------------------------------+

Example 2 – MySQL STR_TO_DATE() Function

Let’s take the second example of this str_to_date() function; as follows:

SELECT STR_TO_DATE('15,12,2019','%m,%d,%Y');

Output-2

+--------------------------------------+
| STR_TO_DATE('15,12,2019','%m,%d,%Y') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

This example returns a null value, the reason for this did not work because we are trying to force the value of 15 months, but only 12 months in a year. This will yield an invalid date value.

Example 3 – MySQL STR_TO_DATE() Function

Let’s take another example with GET_FORMAT() function of MySQL; as follows:

SELECT STR_TO_DATE('12.08.2019', GET_FORMAT(DATE, 'USA'));

Output-3

+----------------------------------------------------+
| STR_TO_DATE('12.08.2019', GET_FORMAT(DATE, 'USA')) |
+----------------------------------------------------+
| 2019-12-08                                         |
+----------------------------------------------------+

Example 4 – MySQL STR_TO_DATE() Function

Let’s take an example using NOW() & CURDATE() function; as follows:

SELECT STR_TO_DATE(NOW(),'%Y-%m-%d %H:%i:%s');

===================================CURDATE()===========================================

SELECT STR_TO_DATE(CURDATE(),'%Y-%m-%d');

Output-4

+--------------------------------------------------------+
|  STR_TO_DATE(NOW(),'%Y-%m-%d %H:%i:%s')                |
+--------------------------------------------------------+
| 2019-05-11 07:32:47                                    |
+--------------------------------------------------------+

===================================CURDATE()===========================================

+--------------------------------------------------------+
|  STR_TO_DATE(CURDATE(),'%Y-%m-%d');                    |
+--------------------------------------------------------+
| 2019-05-11                                             |
+--------------------------------------------------------+

Conclusion

MySQL STR_TO_DATE() Function tutorial, you have learned how to use MySQL STR_TO_DATE() function with various examples.

MySQL STR_TO_DATE() Function