MySQL STR_TO_DATE() Function

In MySQL, the STR_TO_DATE() function converts the specified string to date/time according to the specified format.

The STR_TO_DATE() function is the opposite of the DATE_FORMAT() function.

STR_TO_DATE() Syntax

Here is the syntax of MySQL STR_TO_DATE() function:

STR_TO_DATE(str, format)

Parameters

str
Required. The string that needs to be converted.
format
Required. The format string.

The specifiers shown in the following table may be used in the format string.

symbol illustrate
%a Abbreviation of week ( Sun.. Sat)
%b Abbreviation of month ( Jan.. Dec)
%c month number ( 0.. 12)
%D Day of the month with an English prefix ( 0th, 1st, 2nd, 3rd, …)
%d The two-digit representation of the day of the month ( 00.. 31)
%e Numeric representation of the day of the month ( 0.. 31)
%f microseconds ( 000000.. 999999)
%H hours ( 00.. 23)
%h hours ( 01.. 12)
%I hours ( 01.. 12)
%i minutes ( 00.. 59)
%j day of the year ( 001.. 366)
%k hours ( 0.. 23)
%l hours ( 1.. 12)
%M month name ( January.. December)
%m Two-digit month ( 00.. 12)
%p AM or PM
%r Twelve-hour time ( hh:mm:ssfollowed by AM or PM)
%S seconds ( 00.. 59)
%s seconds ( 00.. 59)
%T Twenty-four hour time ( hh:mm:ss)
%U week of year ( 00.. 53), start of week is Sunday; WEEK() mode 0 in function
%u week of year ( 00.. 53), start of week is Monday; WEEK() mode 1 in function
%V week of the year ( 01.. 53), the start of the week is Sunday; WEEK() mode 2 in the function, for%X
%v week of the year ( 01.. 53), the start of the week is Monday; WEEK() mode 3 in the function, for%x
%W week name ( Sunday.. Saturday)
%w Day of the week ( 0=Sunday.. 6=Saturday)
%X Week of the year, week starts on Sunday, four digits, for%V
%x Week of the year, week starts on Monday, four digits, for%v
%Y four-digit year
%y two-digit year
%% escape%
%x x, other characters listed above

Return value

The STR_TO_DATE() function returns a date/time/datetime.

  • If str cannot be converted to a date or datetime, the STR_TO_DATE() function will return NULL.
  • If any argument is NULL, the STR_TO_DATE() function will return NULL.

STR_TO_DATE() Examples

Here are some examples of the STR_TO_DATE() function.

Example 1: Convert to date

SELECT STR_TO_DATE('2022/02/01', '%Y/%m/%d');

+---------------------------------------+
| STR_TO_DATE('2022/02/01', '%Y/%m/%d') |
+---------------------------------------+
| 2022-02-01                            |
+---------------------------------------+

Example 2: Convert to datetime

SELECT STR_TO_DATE('2022/02/01 10:10:10', '%Y/%m/%d %H:%i:%S');

+---------------------------------------------------------+
| STR_TO_DATE('2022/02/01 10:10:10', '%Y/%m/%d %H:%i:%S') |
+---------------------------------------------------------+
| 2022-02-01 10:10:10                                     |
+---------------------------------------------------------+