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

User · Accepted Answer

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().

User · Answer

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

User · Answer

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

User · Answer

STR TO DATE  12 31 2011     m  d  Y

User · Answer

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

[mysql] how to convert a string to date in mysql?

Examples related to mysql

Examples related to string

Examples related to date