How to Use STR_TO_DATE in MySQL: Syntax and Examples

Use the STR_TO_DATE function in MySQL to convert a string into a date or datetime value by specifying the string and its format. The syntax is STR_TO_DATE(string, format), where format describes the date parts in the string.

Syntax

The STR_TO_DATE function takes two arguments:

• string: The text you want to convert to a date.
• format: The pattern that matches the date format in the string.

The format uses special codes like %Y for a 4-digit year, %m for month, and %d for day.

sql

STR_TO_DATE(string, format)

Example

This example converts the string '31-12-2023' into a date using the format '%d-%m-%Y'. It shows how the function parses the string according to the format and returns a proper date value.

sql

SELECT STR_TO_DATE('31-12-2023', '%d-%m-%Y') AS converted_date;

Output

converted_date
2023-12-31

Common Pitfalls

Common mistakes include:

• Using a format that does not match the string exactly, causing NULL results.
• Mixing date and time formats incorrectly.
• Forgetting that format codes are case-sensitive.

Always ensure the format string matches the input string's layout exactly.

sql

/* Wrong format example returns NULL */
SELECT STR_TO_DATE('12/31/2023', '%d-%m-%Y') AS wrong_date;

/* Correct format example */
SELECT STR_TO_DATE('12/31/2023', '%m/%d/%Y') AS correct_date;

Output

wrong_date
NULL

correct_date
2023-12-31

Quick Reference

Format Code	Meaning	Example
%Y	4-digit year	2023
%y	2-digit year	23
%m	Month (01-12)	12
%d	Day of month (01-31)	31
%H	Hour (00-23)	14
%i	Minutes (00-59)	30
%s	Seconds (00-59)	59

Key Takeaways

Use STR_TO_DATE(string, format) to convert strings to dates in MySQL.

The format string must exactly match the input string's date layout.

Common format codes include %Y for year, %m for month, and %d for day.

Mismatched formats cause NULL results, so double-check your format.

STR_TO_DATE is useful for importing or parsing date strings into date types.