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

Expand|Select|Wrap|Line Numbers

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

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

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by ronverdonk

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

Expand|Select|Wrap|Line Numbers

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

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

Expand|Select|Wrap|Line Numbers

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

Quote:

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_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

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

 
+-------------+-------------+

| name        | MyDate      |

+-------------+-------------+

| MyName      | 25-10-2006  |

+-------------+-------------+

Ronald :cool:

convert string to date

re: convert string to date

re: convert string to date

re: convert string to date

re: convert string to date

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/other

/bytes/about