What can I say? Thank you very much for working on this problem! Helped
me a lot!


-----Original Message-----
From: DL Neil [mailto:[EMAIL PROTECTED]] 
Sent: 29. lokakuuta 2001 12:27
To: Niklas Lampén; Php-DB; Php-General
Subject: Re: [PHP-DB] mySQL table update

> 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, 2))
  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