RE: [PHP-DB] mySQL table update
| From: | Niklas Lampén | Date: | Mon, 29 Oct 2001 13:45:47 +0000 |
| Subject: | RE: [PHP-DB] mySQL table update | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-13808@lists.php.net to get a copy of this message | ||
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