STR_TO_DATE() Function Examples – MySQL

STR_TO_DATE() Function Examples – MySQL

In this MySQL tutorial, we are going to demonstrate to how to use this str_to_date() function and how works the str_to_date() function with several 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:

SpecifierDescription
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th1st2nd3rd, …)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week; WEEK() mode 0
%uWeek (00..53), where Monday is the first day of the week; WEEK() mode 1
%VWeek (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%vWeek (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal % character
%xx, for any “x” not listed above

Conclusion

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

If you have any questions or thoughts to share, use the comment form below to reach us.

Leave a Reply

Your email address will not be published. Required fields are marked *