How to convert a string to date in MySQL?

Question

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

Answer 1

As was told at 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().

Answer 2

STR_TO_DATE('12/31/2011', '%m/%d/%Y')

Answer 3

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

Answer 4

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

Answer 5

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