MySQL - STR_TO_DATE() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL STR_TO_DATE() function accepts a string value and a format string as parameters, extracts the DATE, TIME or, DATETIME values from the given string and returns the result.

Syntax

Following is the syntax of the above function –

STR_TO_DATE(str,format)

Example 1

Following example demonstrates the usage of the STR_TO_DATE() function.

mysql> SELECT STR_TO_DATE('5th Saturday September 2015', '%D %W %M %Y');
+-----------------------------------------------------------+
| STR_TO_DATE('5th Saturday September 2015', '%D %W %M %Y') |
+-----------------------------------------------------------+
|                                                2015-09-05 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT STR_TO_DATE('Sat Sep 05 15', '%a %b %d %y');
+---------------------------------------------+
| STR_TO_DATE('Sat Sep 05 15', '%a %b %d %y') |
+---------------------------------------------+
|                                  2015-09-05 |
+---------------------------------------------+
1 row in set (0.00 sec)

Example 3

Following query converts the string congaing a time value to TIME —

mysql> SELECT STR_TO_DATE('20 Hours 40 Minutes 45 Seconds', '%H Hours %i Minutes %S Seconds');
+---------------------------------------------------------------------------------+
| STR_TO_DATE('20 Hours 40 Minutes 45 Seconds', '%H Hours %i Minutes %S Seconds') |
+---------------------------------------------------------------------------------+
|                                                                        20:40:45 |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Example 4

Following example converts the date-time string to a DATETIME value –

mysql> SELECT STR_TO_DATE('Sep 05 15 10:23:00 PM', '%b %d %y %r');
+-----------------------------------------------------+
| STR_TO_DATE('Sep 05 15 10:23:00 PM', '%b %d %y %r') |
+-----------------------------------------------------+
|                                 2015-09-05 22:23:00 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Example 5

mysql> SELECT STR_TO_DATE('July 22, 1998','%M %d,%Y');
+-----------------------------------------+
| STR_TO_DATE('July 22, 1998','%M %d,%Y') |
+-----------------------------------------+
|                              1998-07-22 |
+-----------------------------------------+
1 row in set (0.00 sec)

Example 6

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth VARCHAR(255),
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

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

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', '5th December 1981, Saturday', 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', '22nd April 1981, Wednesday', 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', '27th October 1977, Thursday', 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', '5th November 1988, Saturday', 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', '30th April 1987, Thursday', 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', '6th December 1988, Tuesday', 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', '30th June 1982, Wednesday', 'Burnley', 'England');

Following query converts the string values in the Date_Of_Birth column into date and prints those —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, STR_TO_DATE(Date_Of_Birth, '%D %M %Y, %W') as FormattedDOB FROM MyPlayers;
+------------+------------+-----------------------------+-------------+--------------+
| First_Name | Last_Name  | Date_Of_Birth               | Country     | FormattedDOB |
+------------+------------+-----------------------------+-------------+--------------+
| Shikhar    | Dhawan     | 5th December 1981, Saturday | India       | 1981-12-05   |
| Jonathan   | Trott      | 22nd April 1981, Wednesday  | SouthAfrica | 1981-04-22   |
| Kumara     | Sangakkara | 27th October 1977, Thursday | Srilanka    | 1977-10-27   |
| Virat      | Kohli      | 5th November 1988, Saturday | India       | 1988-11-05   |
| Rohit      | Sharma     | 30th April 1987, Thursday   | India       | 1987-04-30   |
| Ravindra   | Jadeja     | 6th December 1988, Tuesday  | India       | 1988-12-06   |
| James      | Anderson   | 30th June 1982, Wednesday   | England     | 1982-06-30   |
+------------+------------+-----------------------------+-------------+--------------+
7 rows in set (0.00 sec)

Example 7

Suppose we have created a table named Subscribers with 5 records in it using the following queries –

mysql> CREATE TABLE Subscribers(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDate VARCHAR(255)
);
insert into Subscribers values('Raja', 'Premium', '21st October 20');
insert into Subscribers values('Roja', 'Basic', '26th November 20');
insert into Subscribers values('Puja', 'Moderate', '7th March 21');
insert into Subscribers values('Vanaja', 'Basic', '21st February 21');
insert into Subscribers values('Jalaja', 'Premium', '30th January 21');

In the following example we are passing the column SubscriptionDate as date value to this function –

mysql> SELECT SubscriberName, PackageName, SubscriptionDate, STR_TO_DATE(SubscriptionDate, '%D %M %y') as FormattedDate FROM Subscribers;
+----------------+-------------+------------------+---------------+
| SubscriberName | PackageName | SubscriptionDate | FormattedDate |
+----------------+-------------+------------------+---------------+
| Raja           | Premium     | 21st October 20  | 2020-10-21    |
| Roja           | Basic       | 26th November 20 | 2020-11-26    |
| Puja           | Moderate    | 7th March 21     | 2021-03-07    |
| Vanaja         | Basic       | 21st February 21 | 2021-02-21    |
| Jalaja         | Premium     | 30th January 21  | 2021-01-30    |
+----------------+-------------+------------------+---------------+
5 rows in set (0.00 sec)

Example 8

Suppose we have created a table named SubscribersData with 5 records in it using the following queries –

mysql> CREATE TABLE SubscribersData(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimestamp VARCHAR(255)
);
insert into SubscribersData values('Raja', 'Premium', '2020-10-21 20.53.49');
insert into SubscribersData values('Roja', 'Basic', '2020-11-26 10.13.19');
insert into SubscribersData values('Puja', 'Moderate', '2021-03-07 05.43.20');
insert into SubscribersData values('Vanaja', 'Basic', '2021-02-21 16.36.39');
insert into SubscribersData values('Jalaja', 'Premium', '2021-01-30 12.45.45');

In the following example we are trying to format the SubscriptionDate and SubscriptionTime columns as a single timestamp according to the USA standard –

mysql> SELECT SubscriberName, PackageName,
STR_TO_DATE(SubscriptionTimestamp, GET_FORMAT(TIMESTAMP, 'USA'))
as TIMESTAMP FROM SubscribersData;
+----------------+-------------+---------------------+
| SubscriberName | PackageName | TIMESTAMP           |
+----------------+-------------+---------------------+
| Raja           | Premium     | 2020-10-21 20:53:49 |
| Roja           | Basic       | 2020-11-26 10:13:19 |
| Puja           | Moderate    | 2021-03-07 05:43:20 |
| Vanaja         | Basic       | 2021-02-21 16:36:39 |
| Jalaja         | Premium     | 2021-01-30 12:45:45 |
+----------------+-------------+---------------------+
5 rows in set (0.00 sec)