SQL Tutorial SQL Advanced SQL Database SQL References

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

string Required. Specify the string value to format as a date.
format_mask Required. Specify the format to apply to the string. The following is a list of options for this parameter. It can be used in many combinations.

ValueDescription
%aWeekday name abbreviated (Sun to Sat)
%bMonth name abbreviated (Jan to Dec)
%cMonth as a numeric value (0 to 12)
%DDay of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%dDay of the month as a numeric value (01 to 31)
%eDay of the month as a numeric value (0 to 31)
%fMicroseconds (000000 to 999999)
%HHour (00 to 23)
%hHour (00 to 12)
%IHour (00 to 12)
%iMinutes (00 to 59)
%jDay of the year (001 to 366)
%kHour (00 to 23)
%lHour (1 to 12)
%MMonth name in full (January to December)
%mMonth name as a numeric value (00 to 12)
%pAM or PM
%rTime in 12 hour AM or PM format (hh:mm:ss AM/PM)
%SSeconds (00 to 59)
%sSeconds (00 to 59)
%TTime in 24 hour format (hh:mm:ss)
%UWeek where Sunday is the first day of the week (00 to 53)
%uWeek where Monday is the first day of the week (00 to 53)
%VWeek where Sunday is the first day of the week (01 to 53). Used with %X
%vWeek where Monday is the first day of the week (01 to 53). Used with %X
%WWeekday name in full (Sunday to Saturday)
%wDay of the week where Sunday=0 and Saturday=6
%XYear for the week where Sunday is the first day of the week. Used with %V
%xYear for the week where Monday is the first day of the week. Used with %v
%YYear as a numeric, 4-digit value
%yYear as a numeric, 2-digit value
%#For STR_TO_DATE(), skip all numbers.
%.For STR_TO_DATE(), skip all punctation characters.
%@For STR_TO_DATE(), skip all alpha characters.
%%A literal % character

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:

EmpIDNameCityAgeDate of Joining
1JohnLondon25May 25, 2018
2MarryNew York24October 15, 2018
3JoParis27June 9, 2019
4KimAmsterdam30September 21, 2019
5RameshNew Delhi28October 25, 2019

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:

EmpIDNameCityAgeDate of JoiningSTR_TO_DATE_Value
1JohnLondon25May 25, 20182018-05-25
2MarryNew York24October 15, 20182018-10-15
3JoParis27June 9, 20192019-06-09
4KimAmsterdam30September 21, 20192019-09-21
5RameshNew Delhi28October 25, 20192019-10-25

❮ MySQL Functions

5