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/en/date-and-time-functions.html#function_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:
[code=mysql]
CREATE TABLE txt(theDate varchar(10));

INSERT INTO txt(theDate) VALUES
('2009/04/01'), ('2009/04/02'), ('2009/04/03');[/code]
You could simply do:
[code=mysql]ALTER TABLE txt CHANGE theDate theDate Date;[/code]
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]

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

thanks
nadir

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