MySQL - STR_TO_DATE() Function

The MySQL STR_TO_DATE() function converts the date in string format to the date in a required format.

This function accepts a date value in the string form and a format string as arguments, extracts the DATE, TIME or, DATETIME values from the given string in the given format and returns the result.

There are certain characters with predefined meaning using which you can create a format string argument. They are −

Format Description
%a Weekday name (Sun..Sat)
%b 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

Syntax

Following is the syntax of MySQL STR_TO_DATE() function −

STR_TO_DATE(str,format)

Parameters

This method accepts two parameters. The same is described below −

  • str: The string containing the date or time information.

  • format: The format in which the date or time information in the string is represented.

Return value

This function returns a MySQL date or time value based on the provided string and format.

Example

In the following query, we are using the STR_TO_DATE() function to convert the given string into a date format −

SELECT STR_TO_DATE('5th Saturday September 2023', '%D %W %M %Y')
As Result;

Output

This will produce the following result −

Result
2023-09-05

Example

Following is another example of this function −

SELECT STR_TO_DATE('Sat Sep 05 23', '%a %b %d %y') As Result;

Output

Following is the output −

Result
2023-09-05

Example

Here, we are converting string "20 Hours 40 Minutes 45 Seconds" into a time format using the STR_TO_DATE() function −

SELECT STR_TO_DATE('20 Hours 40 Minutes 45 Seconds', '%H Hours %i Minutes %S Seconds')
As Result;

Output

Following is the output −

Result
20:40:45

Example

The following query converts the date-time string to a DATETIME value −

SELECT STR_TO_DATE('Sep 05 15 10:23:00 PM', '%b %d %y %r')
As Result;

Output

Following is the output −

Result
2015-09-05 22:23:00

Example

In this example, we have created a table named PLAYERS using the following CREATE TABLE query −

CREATE TABLE PLAYERS(
   ID int,
   NAME varchar(255),
   DOB varchar(255),
   Country varchar(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

INSERT INTO PLAYERS VALUES
(1, 'Shikhar Dhawan', '5th December 1981, Saturday', 'India'),
(2, 'Jonathan Trott', '22nd April 1981, Wednesday', 'SouthAfrica'),
(3, 'Kumara Sangakkara', '27th October 1977, Thursday', 'Srilanka'),
(4, 'Virat Kohli', '5th November 1988, Saturday', 'India'),
(5, 'Rohit Sharma', '30th April 1987, Thursday', 'India'),
(6, 'Ravindra Jadeja', '6th December 1988, Tuesday', 'India'),
(7, 'James Anderson', '30th June 1982, Wednesday', 'England');

Execute the below query to fetch all the inserted records in the above-created table −

Select * From PLAYERS;

Following is the PLAYERS table −

ID NAME DOB Country
1 Shikhar Dhawan 5th December 1981, Saturday India
2 Jonathan Trott 22nd April 1981, Wednesday Srilanka
3 Kumara Sangakkara 27th October 1977, Thursday Srilanka
4 Virat Kohli 5th November 1988, Saturday India
5 Rohit Sharma 30th April 1987, Thursday India
6 Ravindra Jadeja 30th April 1987, Thursday India
7 James Anderson 30th June 1982, Wednesday England

Here, we are using the MySQL STR_TO_DATE() function to convert the "DOB" values from their current format to a new format specified format string.

SELECT ID, NAME, DOB, STR_TO_DATE(DOB, '%D %M %Y, %W')
As Result FROM PLAYERS;

Output

The output is displayed as follows −

ID NAME DOB Result
1 Shikhar Dhawan 5th December 1981, Saturday 1981-12-05
2 Jonathan Trott 22nd April 1981, Wednesday 1981-04-22
3 Kumara Sangakkara 27th October 1977, Thursday 1977-10-27
4 Virat Kohli 5th November 1988, Saturday 1988-11-05
5 Rohit Sharma 30th April 1987, Thursday 1987-04-30
6 Ravindra Jadeja 30th April 1987, Thursday 1988-12-06
7 James Anderson 30th June 1982, Wednesday 1982-06-30
Advertisements