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.
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
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 Escape Strings in jQuery
Set NGINX to Catch All Unhandled Virtual Hosts
How to Do Case Insensitive Rewrite in NGINX