MySQL STR_TO_DATE() – Convert Date String to MySQL Date

STR TO DATE Function

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)

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; 

And the output is –

MySQL STR_TO_DATE Basic Example 1

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;

And the output is –

Str To Date Basic Example 2

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; 

And the output is –

Str To Date Order Of Formats 1

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;

And the output is –

Str To Date Order Of Formats 2

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; 

And the output is –

Str To Date Datetime 1

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; 

And the output is –

Str To Date Datetime 2

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; 

And the output is –

Str To Date Format

Working With Tables

Consider the ‘Persons’ table.

Persons Table
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;

And the output is –

Persons Table Table Example

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