convert string to date

[dboyr]

Hy

How can I convert a string like '31-jan-2003' into a valid mysql date? In Oracle, Fox, Microsoft SQL I have to_date('31-jan-2003','dd-mon-yyyy').

There is something like that in MySql ????

[raf]

Hmm. There is something like DATE_FORMAT(date,forma° but i think it onluy runs on shortdate format, so you probably will have to replace the months with numbers (updatestatement). More info
http://www.mysql.com/doc/en/Date_and_time_functions.html

[dboyr]

I got a lot of day , month, year , etc... functions for a date column but I can not convert a string to date.

I got a column that looks like '02;31;2003'. How can I insert that column into a date column?
Do I need a procedure to do that !!!??

[raf]

as far as i know, MySQL doesn't support procedures (as db-objects). Think the easies way is just to replace the ";" with a "-" with an updatestatement. After that, you can treat it as a date. But i think even then, you should best create another variable (dateformat) and update this with the current values+replace
something like

UPDATE table SET newvariable=REPLACE(oldvariable,';','-');

I just make sure that if i'm gonna store datevalues in a variable, i create it as a datevariable, so this are actually avoidable problems ...

[dboyr]

When you import data from another source like Microsoft Fox, Excel, Oracle, or anything else the date format is not in the standard MySql date format.

So I have to make a procedure to do the convertion or can I use the date format functions that MySql has?

[raf]

Well, if you're a big company, you probably will spend loads of money on an ETL tool (extraction-transformation-loading) tool and just sit back and let the machine do it's work, but else, you either need to always use a "universal" dataformat or do some transforming (which sometimes can be a it bit experimenting).

There are some loading tools for MySQL (i've stumbeled in to them on the MySQL site once) but i don't know if there are free ETL tools that support loeding to MySQL
So I have to make a procedure to do the convertion or can I use the date format functions that MySql has?
You can use the MySQL functions. If you're lucky, it's just one or two querys you need to run. (one to create a new variable? + one te make the replaces and update the new variable)