How to Convert String to Date in MySQL

MySQL is a popular database system that allows you to easily work with dates and strings. In many cases, you may need to convert string to date in MySQL. In this article, we will learn a couple of easy ways to do this.

How to Convert String to Date in MySQL

MySQL provides str_to_date() function to convert strings to dates in MySQL. These strings can be literal strings, column names or variables. Here is the syntax for str_to_date() function.

STR_TO_DATE(string, format)

The first argument above is the string that you need to convert while the 2nd argument is the format of the date string. It will return the output as YYYY-MM-DD format. Here is a list of all format specifiers supported by str_to_date() function.

You can use this function in SELECT as well as WHERE clause of your SQL queries.

1. In SELECT clause

Here is an example to use str_to_date() function SELECT clause of your query.

SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y')
FROM    yourtable

For example,

SELECT  STR_TO_DATE(sale_date, '%m/%d/%Y')
FROM    sales;

OR

SELECT STR_TO_DATE('October 10, 2015','%M %d,%Y');

Please note, if the entered date or date string is illegal, then it will return NULL. On the other hand, str_to_date accepts numerous date formats, as long as the format specified in second argument matches that of the date string used in the first argument. Here are some examples.

SELECT STR_TO_DATE("August,5,2017", "%M,%e,%Y");

SELECT STR_TO_DATE("Monday, August 14, 2017", "%W, %M %e, %Y");

SELECT STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s");

2. In WHERE clause

You can also use str_to_date() function in WHERE clause as shown below.

SELECT column_name(s)
  FROM table_name
 WHERE STR_TO_DATE(datefield, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAY

Here is an example.

SELECT *
  FROM sales
 WHERE STR_TO_DATE(created_at, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAY

In this article, we have learnt how to convert string to date using str_to_date() function.

How to Convert String to Date in MySQL