By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,506 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,506 IT Pros & Developers. It's quick & easy.

convert string to date

P: 83
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
Oct 11 '06 #1
Share this Question
Share on Google+
4 Replies


ronverdonk
Expert 2.5K+
P: 4,258
A valid MySQL date would be YYYY-MM-DD, so to convert your string into that use
Expand|Select|Wrap|Line Numbers
  1.  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
  1. 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:
Oct 11 '06 #2

P: 83
A valid MySQL date would be YYYY-MM-DD, so to convert your string into that use
Expand|Select|Wrap|Line Numbers
  1.  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
  1. 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
Oct 11 '06 #3

P: 83
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
Oct 11 '06 #4

ronverdonk
Expert 2.5K+
P: 4,258
When you want an easier heading display for the date you could also format it like
Expand|Select|Wrap|Line Numbers
  1. select name,
  2. date_format(str_to_date(use_date, "%d-%b-%Y"), "%d-%m-%Y") 
  3. as My_Date 
Thus you would get a display like
Expand|Select|Wrap|Line Numbers
  1. +-------------+-------------+
  2. | name        | MyDate      |
  3. +-------------+-------------+
  4. | MyName      | 25-10-2006  |
  5. +-------------+-------------+
Ronald :cool:
Oct 11 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.