MySQL STR_TO_DATE() Function with Example

InMySQL STR_TO_DATE() function; In this tutorial, we are going to demonstrate to how to use this str_to_date() function and how works the str_to_date() function with the help of examples.

MySQL STR_TO_DATE() Function

In MySQL, STR_TO_DATE () function is used to create date values from the various date parts.

When you use this function, you provide a string of different date parts, and another argument that specifies the format in which the date is provided.

Syntax

The syntax of MySQL str_to_date() function of below:

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

Let’s take a first basic example of str_to_date() function for demostration.

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

Let’s take the second example of this str_to_date() function. The format string must match the values provided in the date string. If the string does not match it will return null.

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

Let’s take another example with GET_FORMAT() function of MySQL. In GET_FORMATE() function accept the second argument.

Let’s see the example below

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

We take next example using the NOW() & CURDATE() function. NOW() function returns the datetime value and curdate() is return the date value.

Let’s see the below example

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                                             |
+--------------------------------------------------------+

Example-5

We take an example of str_to_date() function with DateTime value. It will return the date and time.

Let’s see the below example

SELECT STR_TO_DATE('11/05/2019 07:32:47','%d/%m/%Y %H:%i:%s');

Output-5

+--------------------------------------------------------+
| STR_TO_DATE('31/12/1999 09:30:17','%d/%m/%Y %H:%i:%s') |
+--------------------------------------------------------+
| 2019-05-11 07:32:47                                    |
+--------------------------------------------------------+

MySQL – Format String Specifiers

We have provided the list of specifiers for the format string. In addition to these, you can also use literal strings in the format string. See the below list of 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; `WEEK()` mode 0
`%u`	Week (`00`..`53`), where Monday is the first day of the week; `WEEK()` mode 1
`%V`	Week (`01`..`53`), where Sunday is the first day of the week; `WEEK()` mode 2; used with `%X`
`%v`	Week (`01`..`53`), where Monday is the first day of the week; `WEEK()` mode 3; used with `%x`
`%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

Conclusion

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

MySQL STR_TO_DATE() Function with Example