convert string to date

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sang
    New Member
    • Sep 2006
    • 83

    convert string to date

    How can I convert a string like '11-oct-2006' into a valid mysql date?

    the date_format doesnot change the string in requried date format.

    create table sample(name varchar(20),dat e varchar(16));
    insert into sample values('java',' 11-oct-2006');

    but at the time of select statement convert the string into date to change the date format like 11-10-2006.

    so please help me to solve my problem. i am using the dos prompt not in php.

    Thanks in advance
  • ronverdonk
    Recognized Expert Specialist
    • Jul 2006
    • 4259

    #2
    A valid MySQL date would be YYYY-MM-DD, so to convert your string into that use
    Code:
     select str_to_date('10-oct-2006', "%d-%b-%Y");
    In order to get a date formtted DD-MM-YYYY you use
    Code:
    select date_format(str_to_date('10-oct-2006', "%d-%b-%Y"), "%d-%m-%Y");
    Anyway, it is all in the MySQL manual!

    Ronald :cool:

    Comment

    • sang
      New Member
      • Sep 2006
      • 83

      #3
      Originally posted by ronverdonk
      A valid MySQL date would be YYYY-MM-DD, so to convert your string into that use
      Code:
       select str_to_date('10-oct-2006', "%d-%b-%Y");
      In order to get a date formtted DD-MM-YYYY you use
      Code:
      select date_format(str_to_date('10-oct-2006', "%d-%b-%Y"), "%d-%m-%Y");
      Anyway, it is all in the MySQL manual!

      Ronald :cool:

      Thankyou but this already done

      i want to call the field at the date place.

      select date_format(str _to_date('11-oct-2006', "%d-%b-%Y"), "%d-%m-%Y");
      +-----------------------------------------------------------------+
      | date_format(str _to_date('10-oct-2006', "%d-%b-%Y"), "%d-%m-%Y") |
      +-----------------------------------------------------------------+
      | 10-10-2006 |
      +-----------------------------------------------------------------+

      In mytable i insert the date at use_date.
      create table sample(name varchar(20),use _date varchar(16));

      insert into sample values('java',' 11-oct-2006');

      select name,date_forma t(str_to_date(' use_date', "%d-%b-%Y"), "%d-%m-%Y") from sample;

      but it gives the output like this
      +------+--------------------------------------------------------------+
      | name | date_format(str _to_date('use_d ate', "%d-%b-%Y"), "%d-%m-%Y") |
      +------+--------------------------------------------------------------+
      | java | |
      +------+--------------------------------------------------------------+

      how to overcome this pls help
      Thanks
      Sang

      Comment

      • sang
        New Member
        • Sep 2006
        • 83

        #4
        Originally posted by sang
        Thankyou but this already done

        i want to call the field at the date place.

        select date_format(str _to_date('11-oct-2006', "%d-%b-%Y"), "%d-%m-%Y");
        +-----------------------------------------------------------------+
        | date_format(str _to_date('10-oct-2006', "%d-%b-%Y"), "%d-%m-%Y") |
        +-----------------------------------------------------------------+
        | 10-10-2006 |
        +-----------------------------------------------------------------+

        In mytable i insert the date at use_date.
        create table sample(name varchar(20),use _date varchar(16));

        insert into sample values('java',' 11-oct-2006');

        select name,date_forma t(str_to_date(' use_date', "%d-%b-%Y"), "%d-%m-%Y") from sample;

        but it gives the output like this
        +------+--------------------------------------------------------------+
        | name | date_format(str _to_date('use_d ate', "%d-%b-%Y"), "%d-%m-%Y") |
        +------+--------------------------------------------------------------+
        | java | |
        +------+--------------------------------------------------------------+

        how to overcome this pls help
        Thanks
        Sang
        This is also done by using

        select name,date_forma t(str_to_date(u se_date, "%d-%b-%Y"), "%d-%m-%Y") from sample;

        Thanks
        Sang

        Comment

        • ronverdonk
          Recognized Expert Specialist
          • Jul 2006
          • 4259

          #5
          When you want an easier heading display for the date you could also format it like
          Code:
          select name,
          date_format(str_to_date(use_date, "%d-%b-%Y"), "%d-%m-%Y") 
          as My_Date
          Thus you would get a display like
          Code:
          +-------------+-------------+
          | name        | MyDate      |
          +-------------+-------------+
          | MyName      | 25-10-2006  |
          +-------------+-------------+
          Ronald :cool:

          Comment

          Working...