Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Understanding STR_TO_DATE()
2.1.
Syntax of STR_TO_DATE()
3.
Using STR_TO_DATE()
3.1.
Practical Application
4.
Frequently Asked Questions
4.1.
What does the STR_TO_DATE() function do in MySQL?
4.2.
What happens if the string does not match the format in STR_TO_DATE()?
4.3.
Can STR_TO_DATE() handle different date formats?
5.
Conclusion
Last Updated: Mar 27, 2024
Easy

MySQL STR_TO_DATE() Function

Author Gunjan Batra
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

While working with databases, it is very common to deal with dates. In MySQL, the database management system we'll focus on in this article, there's a handy function called STR_TO_DATE(). 

Mysql  STR_TO_DATE() Function

This function is used to convert a string into a date, which can be very useful when the date data is stored as strings. So let's take a closer look at this function.

Understanding STR_TO_DATE()

In MySQL, the STR_TO_DATE() function is used to convert a string into a date or time value, depending on the format string specified. This is particularly useful when you receive a date in a format that isn't directly compatible with MySQL's date or datetime types.

Syntax of STR_TO_DATE()

The STR_TO_DATE() function uses the following syntax:

STR_TO_DATE(str, format)

In this syntax:

str is the string value to be converted into date or time.

format is the string that determines how the date or time should be formatted.

The format string can contain several placeholders for parts of the date, like %d for day, %m for month, and %Y for four-digit year.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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
 

otuput

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

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

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:

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!!
 

Previous article
MySQL TRIM() Function
Next article
MySQL UNIX_TIMESTAMP() Function
Live masterclass