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.yyyy
b) d.mm.yyyy
c) dd.m.yyyy
d) d.m.yyyy

So this is what I've tried to do to convert the dates to yyyy-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]

Reply via email to