MySQL STR_TO_DATE() Function

Using STR_TO_DATE()

Let's look at a simple example:

SELECT STR_TO_DATE('31-01-2023', '%d-%m-%Y');

In this example, STR_TO_DATE() will return '2023-01-31', which is a MySQL-compatible date.

Output
 

If the string does not match the format exactly, MySQL returns NULL.

Practical Application

Consider a table named 'events', which stores event data but unfortunately stores dates as strings:

CREATE TABLE events(
   id INT PRIMARY KEY,
   name VARCHAR(100),
   event_date VARCHAR(20)
);

Output

You can convert these string dates into actual date values using STR_TO_DATE():

SELECT id, name, STR_TO_DATE(event_date, '%d-%m-%Y') AS actual_date

FROM events;

Output

This will return a list of all events, with their dates converted from strings into MySQL date values.

Frequently Asked Questions

What does the STR_TO_DATE() function do in MySQL?

The STR_TO_DATE() function in MySQL converts a string into a date based on the format string provided.

What happens if the string does not match the format in STR_TO_DATE()?

If the string does not match the format string, STR_TO_DATE() returns NULL.

Can STR_TO_DATE() handle different date formats?

Yes, STR_TO_DATE() can handle different date formats. The format string defines how the date is read from the string.

Conclusion

Understanding and using the STR_TO_DATE() function is crucial when handling dates stored as strings in MySQL databases. It offers a way to convert these strings into MySQL-compatible date values, allowing for more meaningful data manipulation and analysis. By mastering STR_TO_DATE(), you'll make your SQL queries more robust and your database interactions more efficient.

Here are some more related articles:

• MySQL: Case Statement 
• MySQL Introduction
• MySQL Features

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning!!