In this tutorial, we will study the MySQL STR_TO_DATE() function. Suppose you have a table of details of a few people. One of the columns in the table is DOB which specifies the date of birth of each individual as a string.
An example of the date string format is – ‘August 29, 2020’.
You have been tasked with converting this string value to a date value so that you can perform some date operations on it. This is where we use the MySQL STR_TO_DATE() function.
The STR_TO_DATE() function returns a date/datetime value based on a string and a format. Let us understand this better with the syntax and a few examples.
Syntax of MySQL STR_TO_DATE()
STR_TO_DATE(string, format)Code language: SQL (Structured Query Language) (sql)Where, ‘string’ is the string that has to be formatted to a date value and
‘format’ is the format to use while formatting the string to a date value.
The ‘format’ can be one or a combination of the following –
- %a – Abbreviated weekday name (Sun to Sat)
- %b – Abbreviated month name (Jan to Dec)
- %c – Numeric month name (0 to 12)
- %D – Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, …)
- %d – Day of the month as a numeric value (01 to 31)
- %e – Day of the month as a numeric value (0 to 31)
- %f – Microseconds (000000 to 999999)
- %H – Hour (00 to 23)
- %h – Hour (00 to 12)
- %I – Hour (00 to 12)
- %i – Minutes (00 to 59)
- %j – Day of the year (001 to 366)
- %k – Hour (0 to 23)
- %l – Hour (1 to 12)
- %M – Month name in full (January to December)
- %m – Month name as a numeric value (00 to 12)
- %p – AM or PM
- %r – Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
- %S – Seconds (00 to 59)
- %s – Seconds (00 to 59)
- %T – Time in 24 hour format (hh:mm:ss)
- %U – Week where Sunday is the first day of the week (00 to 53)
- %u – Week where Monday is the first day of the week (00 to 53)
- %V – Week where Sunday is the first day of the week (01 to 53). Used with %X
- %v – Week where Monday is the first day of the week (01 to 53). Used with %x
- %W – Weekday name in full (Sunday to Saturday)
- %w – Day of the week where Sunday=0 and Saturday=6
- %X – Year for the week where Sunday is the first day of the week. Used with %V
- %x – Year for the week where Monday is the first day of the week. Used with %v
- %Y – Year as a numeric, 4-digit value
- %y – Year as a numeric, 2-digit value
Examples of MySQL STR_TO_DATE()
Let us kick things off with a couple of basic examples. Suppose you have the string – ‘15 02 2021’ where 15 is the day, 02 is the month and 2021 represents the year.
Let us convert this to a date value. We will use the SELECT statement and an alias called ‘Result’.
The query is –
SELECT STR_TO_DATE('15 02 2021', '%d %m %Y') AS Result; Code language: SQL (Structured Query Language) (sql)And the output is –
The format tells the MySQL STR_TO_DATE() function in what way should the string be formatted.
Let us look at another example. This time we have the string – ‘May 5, 2020’. To convert this to a date value, we will use the format – ‘%M %d, %Y’. Yes, you need to mention that comma in the format as it is in the string. The query is –
SELECT STR_TO_DATE('May 5, 2020', '%M %d, %Y') AS Result;Code language: SQL (Structured Query Language) (sql)And the output is –
Using the Wrong Date Format
Building on the earlier example, what if we forgot to mention the comma in the format? Consider the string – ‘15,02,2021’. What if we use the format – ‘%M %d %Y’?
This format is incorrect as the numbers in the string are separated by a comma rather than a whitespace. Let us see the query for this.
SELECT STR_TO_DATE('15,02,2021', '%M %d %Y') AS Result; Code language: SQL (Structured Query Language) (sql)And the output is –
When we use a wrong ‘format’ parameter value for the STR_TO_DATE() function, we get NULL as our output. Let us see another example of this.
SELECT STR_TO_DATE('May 5, 2020', '%m %d, %Y') AS Result;Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL STR_TO_DATE() with Datetime Values
If you have a date and a time value in a string, you can convert it into a datetime data type value. Let us see a couple of examples of this. Consider the query –
SELECT STR_TO_DATE('12 Aug 2021 5PM', '%d %b %Y %h%p') AS Result; Code language: SQL (Structured Query Language) (sql)And the output is –
Let us look at another example. We have the string – ‘February 10 2021 6:25:05 AM’. Let us convert it into a datetime value. The query is –
SELECT STR_TO_DATE('February 10 2021 6:25:05 AM', '%M %d %Y %h:%i:%s %p') AS Result; Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL STR_TO_DATE() With the GET_FORMAT() function
Another application of the GET_FORMAT() function is with the STR_TO_DATE() function. The GET_FORMAT() function can be used in the ‘format’ parameter. Let us see a couple of examples of this. Consider the queries –
SELECT STR_TO_DATE('10.16.2020', GET_FORMAT(DATE, 'USA')) AS Result;
SELECT STR_TO_DATE('10.01.2021', GET_FORMAT(DATE, 'EUR')) AS Result; Code language: SQL (Structured Query Language) (sql)And the output is –
Working With Tables
Consider the ‘Persons’ table.
Now let us come back to the problem I mentioned in the beginning. Suppose you have a table of details of a few people. One of the columns in the table is DOB which specifies the date of birth of each individual as a string. An example of the date string format is – ‘August 29, 2020’. You have been tasked with converting this string value to a date value so that you can perform some date operations on it. Let us use the STR_TO_DATE() function for this with the UPDATE statement. The query is –
UPDATE Persons SET DOB=STR_TO_DATE(DOB, '%d %M %Y');
SELECT * FROM Persons;Code language: SQL (Structured Query Language) (sql)And the output is –
Conclusion
Formatting date and time values is a very important operation in MySQL and is widely used in a lot of use cases – especially when we have to make the date and time value presentable for the user. I would highly encourage you to practice a couple of examples of this function.
References
- MySQL Official documentation on the
STR_TO_DATE()function.