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 · Answer

STR TO DATE  12 31 2011     m  d  Y

User · 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    gt  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

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

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

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

Examples related to mysql

Examples related to string

Examples related to date