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]

Reply via email to