RE: [PHP-DB] mySQL table update
I have a problem here which I can not find an answer. My date fields (EmailD in this case) can be in four different formats: a) dd.mm. b) d.mm. c) dd.m. d) d.m. So this is what I've tried to do to convert the dates to -mm-dd format: INSERT INTO feCompsT (ID, Company, EmailD) SELECT ID, Company, CONCAT_WS( -, ### Pick the year part, last four charters RIGHT( EmailD, 4 ), ### Trying to pick the part from first dot to second dot ### 01.01.2001 or 1.1.2001 etc... ###^^ ^ ### This is the problem part. What am I doing wrong? ### It doesn't even work without +1 and -1 thingies SUBSTRING( EmailD, POSITION( ., EmailD )+1, POSITION( ., SUBSTRING( EmailD, POSITION( ., EmailD )+1 ) )-1 ), ### And finally pick the part before first dot LEFT( EmailD, POSITION( ., EmailD )-1 ) ) FROM feComps LIMIT 50 Thanks in advance! Niklas -- 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]
RE: [PHP-DB] mySQL table update
Ok, found the problem.. I've been fighting with this for like an hour but the error was here: POSITION() should be LOCATION().. Way to go Niklas! :) Niklas -Original Message- From: Niklas Lampén [mailto:[EMAIL PROTECTED]] Sent: 29. lokakuuta 2001 15:46 To: Php-DB Subject: RE: [PHP-DB] mySQL table update I have a problem here which I can not find an answer. My date fields (EmailD in this case) can be in four different formats: a) dd.mm. b) d.mm. c) dd.m. d) d.m. So this is what I've tried to do to convert the dates to -mm-dd format: INSERT INTO feCompsT (ID, Company, EmailD) SELECT ID, Company, CONCAT_WS( -, ### Pick the year part, last four charters RIGHT( EmailD, 4 ), ### Trying to pick the part from first dot to second dot ### 01.01.2001 or 1.1.2001 etc... ###^^ ^ ### This is the problem part. What am I doing wrong? ### It doesn't even work without +1 and -1 thingies SUBSTRING( EmailD, POSITION( ., EmailD )+1, POSITION( ., SUBSTRING( EmailD, POSITION( ., EmailD )+1 ) )-1 ), ### And finally pick the part before first dot LEFT( EmailD, POSITION( ., EmailD )-1 ) ) FROM feComps LIMIT 50 Thanks in advance! Niklas -- 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] -- 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]
Re: [PHP-DB] mySQL table update
I have a large table with quite many dates in format dd.mm. (don't ask why :) and I want to convert them into -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 = -00-00 Date2 = -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. format). I was a little surprised that I couldn't structure the commands: ... select... substring(DATE1, 7, 4) as DATE... concat_ws(/, DATE, ... 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]
