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]