How to convert a string to date in MySQL?
MysqlStringDateMysql Problem Overview
I have a string column which acts as a date and I want to select it as a DATE
.
Is it possible?
My sample data format would be:
> month/day/year
-> 12/31/2011
Mysql Solutions
Solution 1 - Mysql
As was told at https://stackoverflow.com/questions/1908394/mysql-using-a-string-column-with-date-text-as-a-date-field, you can do
SELECT STR_TO_DATE(yourdatefield, '%m/%d/%Y')
FROM yourtable
You can also handle these date strings in WHERE
clauses. For example
SELECT whatever
FROM yourtable
WHERE STR_TO_DATE(yourdatefield, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAY
You can handle all kinds of date/time layouts this way. Please refer to the format specifiers for the DATE_FORMAT()
function to see what you can put into the second parameter of STR_TO_DATE()
.
Solution 2 - Mysql
STR_TO_DATE('12/31/2011', '%m/%d/%Y')
Solution 3 - Mysql
Here's another two examples.
To output the day, month, and year, you can use:
select STR_TO_DATE('14/02/2015', '%d/%m/%Y');
Which produces:
>2015-02-14
To also output the time, you can use:
select STR_TO_DATE('14/02/2017 23:38:12', '%d/%m/%Y %T');
Which produces:
>2017-02-14 23:38:12
Solution 4 - Mysql
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
use the above page to refer more Functions in MySQL
SELECT STR_TO_DATE(StringColumn, '%d-%b-%y')
FROM table
say for example use the below query to get output
SELECT STR_TO_DATE('23-feb-14', '%d-%b-%y') FROM table
For String format use the below link
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
Solution 5 - Mysql
The following illustrates the syntax of the STR_TO_DATE()
function:
STR_TO_DATE(str,fmt);
The STR_TO_DATE()
converts the str string into a date value based on the fmt format string. The STR_TO_DATE()
function may return a DATE , TIME, or DATETIME value based on the input and format strings. If the input string is illegal, the STR_TO_DATE()
function returns NULL.
The following statement converts a string into a DATE value.
SELECT STR_TO_DATE('21,5,2013','%d,%m,%Y');
Based on the format string ‘%d, %m, %Y’, the STR_TO_DATE()
function scans the ‘21,5,2013’ input string.
- First, it attempts to find a match for the %d format specifier, which is a day of the month (01…31), in the input string. Because the number 21 matches with the %d specifier, the function takes 21 as the day value.
- Second, because the comma (,) literal character in the format string matches with the comma in the input string, the function continues to check the second format specifier %m , which is a month (01…12), and finds that the number 5 matches with the %m format specifier. It takes the number 5 as the month value.
- Third, after matching the second comma (,), the
STR_TO_DATE()
function keeps finding a match for the third format specifier %Y , which is four-digit year e.g., 2012,2013, etc., and it takes the number 2013 as the year value.
The STR_TO_DATE()
function ignores extra characters at the end of the input string when it parses the input string based on the format string. See the following example:
SELECT STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y');
More Details : Reference