How to use the MySQL STR_TO_DATE() function

In this article, we will learn how to use the MySQL STR_TO_DATE() function, which converts a string to a date or datetime value, based on a specified format.

Posted on

In this article, we will learn how to use the MySQL STR_TO_DATE() function, which converts a string to a date or datetime value, based on a specified format. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with strings and dates.

Syntax

The syntax of the STR_TO_DATE() function is as follows:

STR_TO_DATE(str, format)

The str parameter can be any string expression that represents a date or datetime value. The format parameter can be any string expression that specifies the format of the date or datetime value. The format parameter can contain any of the date and time format specifiers that are supported by MySQL. If the str or format parameter is NULL, the function returns NULL. The STR_TO_DATE() function returns a date or datetime value that corresponds to the given string and format. If the string does not match the format, or the format is invalid, the function returns NULL. For example, STR_TO_DATE('15/01/2023', '%d/%m/%Y') returns ‘2023-01-15’, which is a date value.

Examples

Let’s see some examples of how to use the STR_TO_DATE() function in MySQL.

Example 1: Convert a string to a date value

We can use the STR_TO_DATE() function to convert a string to a date value, based on a specified format. For example:

SELECT STR_TO_DATE('15/01/2023', '%d/%m/%Y') AS result;

This query will convert the string ‘15/01/2023’ to a date value, based on the format ‘%d/%m/%Y’, which means day/month/year. The query will return ‘2023-01-15’, which is a date value in the standard MySQL format.

Example 2: Convert a string to a datetime value

We can use the STR_TO_DATE() function to convert a string to a datetime value, based on a specified format. For example:

SELECT STR_TO_DATE('15/01/2023 10:02:34', '%d/%m/%Y %H:%i:%s') AS result;

This query will convert the string ‘15/01/2023 10:02:34’ to a datetime value, based on the format ‘%d/%m/%Y %H:%i:%s’, which means day/month/year hour:minute:second. The query will return ‘2023-01-15 10:02:34’, which is a datetime value in the standard MySQL format.

Example 3: Convert a string to a date value with a different format

We can use the STR_TO_DATE() function to convert a string to a date value, based on a different format than the standard MySQL format. For example:

SELECT STR_TO_DATE('January 15, 2023', '%M %d, %Y') AS result;

This query will convert the string ‘January 15, 2023’ to a date value, based on the format ‘%M %d, %Y’, which means month name day, year. The query will return ‘2023-01-15’, which is a date value in the standard MySQL format.

Example 4: Convert a string to a date value with a partial format

We can use the STR_TO_DATE() function to convert a string to a date value, based on a partial format that does not specify all the components of the date. For example:

SELECT STR_TO_DATE('15/01', '%d/%m') AS result;

This query will convert the string ‘15/01’ to a date value, based on the format ‘%d/%m’, which means day/month. The query will return ‘2023-01-15’, which is a date value in the standard MySQL format. Note that the function assumes the current year as the default year for the date value.

Example 5: Convert a string to a date value with an invalid format

We can use the STR_TO_DATE() function to convert a string to a date value, based on an invalid format that does not match the string. For example:

SELECT STR_TO_DATE('15/01/2023', '%m/%d/%Y') AS result;

This query will try to convert the string ‘15/01/2023’ to a date value, based on the format ‘%m/%d/%Y’, which means month/day/year. However, this format does not match the string, since the string has the day before the month. The query will return NULL, since the function cannot parse the string with the given format.

There are some other functions that are related to the STR_TO_DATE() function, and can be useful for working with strings and dates. Here are some of them:

  • DATE_FORMAT(): This function returns the date or datetime value formatted according to a specified format. For example, DATE_FORMAT('2023-01-15', '%d/%m/%Y') returns ‘15/01/2023’.
  • DATE(): This function returns the date part of a date or datetime value. For example, DATE('2023-01-15 10:02:34') returns ‘2023-01-15’.
  • TIME(): This function returns the time part of a date or datetime value. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’.
  • YEAR(): This function returns the year of a date or datetime value. For example, YEAR('2023-01-15') returns 2023.
  • MONTH(): This function returns the month of a date or datetime value. For example, MONTH('2023-01-15') returns 1.
  • DAY(): This function returns the day of the month for a date or datetime value. For example, DAY('2023-01-15') returns 15.

Conclusion

In this article, we learned how to use the MySQL STR_TO_DATE() function, which converts a string to a date or datetime value, based on a specified format. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with strings and dates.