What can I say? Thank you very much for working on this problem! Helped me a lot!
Niklas -----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 Niklas, > At least tell me what to look for in the manual. :) 7 MySQL Language Reference SELECT SUBSTRING() INSERT INTO CONCAT_WS() 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) select NAME, 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: ... select... 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...? Regards, =dn -- 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]
