469,942 Members | 2,606 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,942 developers. It's quick & easy.

convert string to date

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
4 82613
ronverdonk
4,258 Expert 4TB
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
sang
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
sang
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
4,258 Expert 4TB
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.

Similar topics

2 posts views Thread by Hector A | last post: by
2 posts views Thread by Franck | last post: by
12 posts views Thread by DC Gringo | last post: by
4 posts views Thread by perryclisbee via AccessMonster.com | last post: by
6 posts views Thread by vunet.us | last post: by
8 posts views Thread by deepak_kamath_n | last post: by
4 posts views Thread by Ashraf Ansari | last post: by
4 posts views Thread by =?Utf-8?B?YW5kcmV3?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.