Hello,
I have a field that have wrong typed data, for example the date in that
field looks like 20-04-2006 (day month year) and i need to convert it to
2006-04-20.
What is a possible way to do this ?
thx
Peter
Peter van Oord van der Vlies wrote:
> Hello,
>
> I have a field that have wrong typed data, for example the date in that
> field looks like 20-04-2006 (day month year) and i need to convert it to
> 2006-04-20.
>
> What is a possible way to do this ?
Read about the STR_TO_DATE() function on this page of the docs:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html
Regards,
Bill K.
Bill Karwin schreef:
> Peter van Oord van der Vlies wrote:
>> Hello,
>>
>> I have a field that have wrong typed data, for example the date in
>> that field looks like 20-04-2006 (day month year) and i need to
>> convert it to
>> 2006-04-20.
>>
>> What is a possible way to do this ?
>
> Read about the STR_TO_DATE() function on this page of the docs:
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html
>
> Regards,
> Bill K.
I have read that but they are still working on mysql 4.0.24 :(
thx anyway
Peter
Peter van Oord van der Vlies wrote:
> I have read that but they are still working on mysql 4.0.24 :(
Oh yeah. Okay, then you could use other string functions that were
implemented in that version of MySQL. For example:
SELECT CONCAT(SUBSTRING('20-04-2006', 7), '-', SUBSTRING('20-04-2006',
4, 2), '-', SUBSTRING('20-04-2006', 1, 2));
Regards,
Bill K.
Bill Karwin schreef:
> Peter van Oord van der Vlies wrote:
>> I have read that but they are still working on mysql 4.0.24 :(
>
> Oh yeah. Okay, then you could use other string functions that were
> implemented in that version of MySQL. For example:
>
> SELECT CONCAT(SUBSTRING('20-04-2006', 7), '-', SUBSTRING('20-04-2006',
> 4, 2), '-', SUBSTRING('20-04-2006', 1, 2));
>
> Regards,
> Bill K.
Ok that will do the trick, now i have 1 other problem. I found in that
table also other values.
I need to be sure that i only select the values that contains this
format. Is that possible ?
Peter
Peter van Oord van der Vlies wrote:
> Ok that will do the trick, now i have 1 other problem. I found in that
> table also other values.
>
> I need to be sure that i only select the values that contains this
> format. Is that possible ?
You can use regular expressions to match only the NN-NN-NNNN formats:
WHERE date_col REGEXP '[0-9]{2}-[0-9]{2}-[0-9]{4}'
But it's not clear if these are guaranteed to be DD-MM-YYYY. They could
be MM-DD-YYYY, and if so, there are cases where the format cannot be
determined (e.g. '02-02-2001').
At this point, it starts to sound like you should do this in application
code, and find some application library that does more sophisticated
date parsing.
I'm not sure what application language you're using, but when I use
Perl, I use the Date::Manip module, which has a very flexible
date-parsing function.
Regards,
Bill K.