convert varchar to date 1

[thompom]

hi - have a varchar field that i stupidly put dates in and now i need to convert the type to date

this should do it but i get an error can someone tell me why - the table is called eventlog, the varchar field is called update2 and the new column is called updatenew.
my dates are in DD/MM/YYYY format and i want to convert them to YYYY-MM-DD.

thanks MG
version 5.0.7-beta-nt

update eventlog as T1
set updatenew =
 (select str_to_date(update2)from eventlog as T2)
where t1.eventid = t2.eventid

error

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')from eventlog as T2)
where t1.eventid = t2.eventid' at line 3
(0 ms taken)

 

[piti]

if nothing else, there is the date format missing in the str_to_date() function

 

[r937]

update eventlog 
   set updatenew = str_to_date(update2,'%Y-%m-%d')

r937.com | rudy.ca

 

[thompom]

hi - thanks for your replies

r937 - says 0 rows affected

 

[thompom]

hi - my table setup is

CREATE TABLE `eventlog` (
  `eventid` int(11) NOT NULL auto_increment,
  `cusname` varchar(100) default NULL,
  `cushometel` varchar(20) default NULL,
  `cusmobtel` varchar(20) default NULL,
  `reg` varchar(8) default NULL,
  `makeid` int(5) default NULL,
  `model` varchar(100) default NULL,
  `jobno` int(6) default NULL,
  `datein` varchar(20) default NULL,
  `eventq1` text,
  `eventq2` text,
  `eventq3` text,
  `eventq4` text,
  `eventq5` text,
  `eventq6` text,
  `update2` varchar(20) default NULL,
  `doneid` int(5) default NULL,
  `bookedby` varchar(200) default NULL,
  `updatenew` date default NULL,
  PRIMARY KEY  (`eventid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

[r937]

that's quite interesting

could you also please show a couple of rows, so that we may see the actual values in update2 that need to be converted

r937.com | rudy.ca

 

[thompom]

hi - yeah no probs,

(3,'dstyugftyhXXXX','11',NULL,'frgrg',57,'frgrgrggr',3333,'15/08/2007','fergerg','egregegte','gegergetg','rgegrge','rgegege','gegregerge','30/06/2007',1,NULL,NULL),
(4,'Mark Griffiths','01202777777','07879418728','hj55jrx',57,'Astra',11111,'08/05/2007','kjhgfdsdfghjklkhgfd','foiuytrertyuiol','iogfdfghjklkjhgfdfgj','igfdsdfghjkjhgf','hjkl;ljhgfdfghjkl','poiuytredfghjklkjhgfd','30/06/2007',2,NULL,NULL),(5,'wd3d','333',NULL,NULL,13,'dddd',2222,'21/05/2007',NULL,NULL,NULL,NULL,NULL,NULL,'02/07/2007',1,'Nick Broughton',NULL),(6,'asdfghj','333',NULL,NULL,4,'defrerf',345,'29/06/2007','wert','werty','wertyu','wertyui','w34er5tyui','w3e4rtyui','27/06/2007',1,'Nick Broughton',NULL),(7,'ertg','r44','4','4',4,'44',44,'29/06/2007','ertyhuiko','r4ty6ui8',NULL,NULL,NULL,NULL,'01/07/2007',1,'Nick Broughton',NULL),(8,'defrgthyuj','34567','456',NULL,4,'456',567,'29/06/2007',NULL,NULL,NULL,NULL,NULL,NULL,'27/07/2007',1,'Nick Broughton',NULL);

 

[r937]

ah, my bad

use str_to_date(update2,'%d/%m/%Y') instead

;-)


r937.com | rudy.ca

 

[thompom]

hi the statement now looks like

update eventlog set updatenew = str_to_date(update2,'%d-%m-%Y')

but i still get 0 rows affected, any ideas welcome

 

[r937]

it appears that you substituted dashes where i suggested slashes

r937.com | rudy.ca

 

[thompom]

thanks rudy - again!