Posted by "Richard Lynch" on 12/30/05 22:51

On Tue, December 27, 2005 8:01 am, William Stokes wrote:
> I have a DB that has dates stored in a table. The date field type is
> Varchar
> (don't ask why... :( The dates are stored using european date format
> DD.MM.YYYY. I need somehow convert the dates to the format MySQL uses
> (YYYY-MM-DD) and re-store them to a table which has a real date column
> for
> them.

alter table crappy add whatdate date;
update crappy set date = ws_cat('-', substring(eurodate,
7,4),substring(eurodate,3,2),substring(eurodate,1,2));

Once you have a REAL date column, date_format from
http://dev.mysql.com/ is your friend.

> If I can get this done how can I still display the dates in european
> format
> at my web page? Do I have to break the date to pieces and some how
> re-arrange it to the DD.MM.YYYY format or is the some clever function
> for
> this that can automatically do this?

select date_format(whatdate, '%d.%m.%Y') from nice_table;

Personally, it's given the *WORLD* wide web, I'd suggest letting the
visitor choose their date format, rather than forcing them to use the
format that happens to be in vogue in the country hosting the site...
:-)

Not that I've ever had time to do that, mind you, but it's what I'd
recommend :-) :-) :-)

--
Like Music?
http://l-i-e.com/artists.htm

[Back to original message]