convert string to date

[nady]

Hi,
I have a database in mysql i have column which is text and i need to convert it to date for query purposes.

the format for the string in the column is dd/mm/yyyy i need to convert this to date.

please help

[code green]

MySql STR_TO_DATE
http://dev.mysql.com/doc/refman/5.0/...on_str-to-date

[nady]

thanks for the reply
but i want to search also and it does not work if i want to run a query with where clause.

[Atli]

Hi.

If your date is in the format: "yyyy/mm/dd", you can simply alter the structure of the table, replacing the text type with Date.

For example, if you had this table:

CREATE TABLE txt(theDate varchar(10));
 
INSERT INTO txt(theDate) VALUES
('2009/04/01'), ('2009/04/02'), ('2009/04/03');

You could simply do:

ALTER TABLE txt CHANGE theDate theDate Date;

Which should automatically convert your text dates over to a MySQL recognized date format, meaning that MySQL can actually process the values as dates and use them for searches and sorting.

mysql> SELECT * FROM txt;
+------------+
| theDate    |
+------------+
| 2009-04-01 |
| 2009-04-02 |
| 2009-04-03 |
+------------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM txt WHERE DAY(theDate) > 1;
+------------+
| theDate    |
+------------+
| 2009-04-02 |
| 2009-04-03 |
+------------+
2 rows in set (0.01 sec)

Before you do this!
Remember to back up your data... just in case something doesn't go as expected.

[nady]

Hi,
thanks for the reply it works with yyyy/mm/dd
but how to apply the same for dd/mm/yyyy

thanks
nadir

[gregerly]

You will probably be better off using the proper format instead of hacking it to get it working a way it's not supposed to. I would use whatever language your using to interact with the database to alter the format and insert the data as yyyy-mm-dd. I use php, so I'll show you a little example:

//date your working with
$date = '09/22/1979';
 
//create an array of the date pieces
$temp = explode('/',$date);
 
//reformat the date to work with mysql
$formatted_date = $temp[2] . '-' . $temp[1] . '-' . $temp[0];
 
//now do something with your new formatted date

Again, I don't know what language your using to interact with the database, but I imagine you can accomplish the same with just about any language.

Good luck!

[mwasif]

@nady
MySQL does not support this date format. You must have to use date in yyyy/mm/dd format.