> I have a large table with quite many dates in format dd.mm.yyyy (don't
> ask why :) and I want to convert them into yyyy-mm-dd for obvious
> reasons. How can I do this the smartest way? I know I could do it with
> php and update it row by row but that doesn't seem too smart. Can I
> somehow do a copy from table to table at the same time?
> Table 1:
> Name = John Doe
> Date1 = 01.01.2001
> Date2 = 02.02.2001
> Table 2:
> Name = NULL
> Date1 = 0000-00-00
> Date2 = 0000-00-00
> And now I want to set Table 2 to this:
> Name = John Doe
> Date1 = 2001-01-01 // Value from Table 1
> Date2 = 2001-02-02 // Value from Table 1


> At least tell me what to look for in the manual. :)
7 MySQL Language Reference

Used MySQL-Front as a convenient prototyping tool. Created the two tables and slotted 
in sample data - would be
big mistake/more time-consuming trying to work with full-sized files (and if you do, 
hint: use LIMIT).

Decided to approach the problem as a 'game of two halves'. First worked on the SELECT 
statement to pull rows off
the table and format the date components using substring(). Then tried to figure out 
how to get the results into
Table 2 - first of all as separate fields, then using concat_ws() to combine them into 
one. Only worked on one
date column - figured the other was simply a replication task...

Thus a part-solution:

insert into TBL_NIKLAS2 (NAME, DATE1)
    concat_ws("/", substring(DATE1, 7, 4), substring(DATE1, 4, 2), substring(DATE1, 1, 
  from TBL_NIKLAS1

(NB solution fails if existing dates are not strings and/or dates do not conform to 
dd.mm.yyyy format).

I was a little surprised that I couldn't structure the commands:
    substring(DATE1, 7, 4) as DATEYYYY...
    concat_ws("/", DATEYYYY, ...

which seemed a logical/self-documenting way to go about it, but someone more familiar 
with SQL than I will have
to explain why...?


PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to