convert string to date

[sang]

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),date 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]

A valid MySQL date would be YYYY-MM-DD, so to convert your string into that use

 select str_to_date('10-oct-2006', "%d-%b-%Y");

In order to get a date formtted DD-MM-YYYY you use

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:

[sang]

A valid MySQL date would be YYYY-MM-DD, so to convert your string into that use

 select str_to_date('10-oct-2006', "%d-%b-%Y");

In order to get a date formtted DD-MM-YYYY you use

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_format(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_date', "%d-%b-%Y"), "%d-%m-%Y") |
+------+--------------------------------------------------------------+
| java | |
+------+--------------------------------------------------------------+

how to overcome this pls help
Thanks
Sang

[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_format(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_date', "%d-%b-%Y"), "%d-%m-%Y") |
+------+--------------------------------------------------------------+
| java | |
+------+--------------------------------------------------------------+

how to overcome this pls help
Thanks
Sang

This is also done by using

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

Thanks
Sang

[ronverdonk]

When you want an easier heading display for the date you could also format it like

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

+-------------+-------------+
| name        | MyDate      |
+-------------+-------------+
| MyName      | 25-10-2006  |
+-------------+-------------+

Ronald :cool: