SELECT concat( substring_index(DATE,'-',-1),
'-',
lpad(
field(substring_index(substring_index(DATE,'-',2),'-',-1),
'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug',
'Sep','Oct','Nov','Dec'),
2,'0'),
'-',
lpad(substring_index(DATE,'-',1),2,0),
Replace DATE by the date string you want to have converted (e.g. via a
variable from an API or a column name from a
select/update/insert..select
Cheers
/rudy
-----Original Message-----
From: Jonathan Patton [mailto:[EMAIL PROTECTED]
Sent: woensdag 16 juli 2003 15:07
To: [EMAIL PROTECTED]
Subject: date conversion
I need to convert dates in the format below into mysql date format. If I
try to change the column type to date, it wipes out the data.
+-------------+
| 26-Jan-2001 |
| 26-Jan-2001 |
| 15-Apr-2001 |
| 1-Nov-2001 |
| 31-Oct-2001 |
| 26-Jan-2001 |
| 22-Jun-2001 |
| 23-Jul-2000 |
| 1-Nov-2001 |
| 31-May-2000 |
| 17-Nov-2000 |
| 10-May-2001 |
| 15-Apr-2001 |
| 17-May-2001 |
| 29-Mar-2000 |
| 30-Aug-2001 |
| 18-May-2000 |
| 11-Apr-2000 |
| 1-Nov-2001 |
| 24-Feb-2000 |
+-------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]