Mysql – how to update date field in a record to null

datedate formatMySQL

I have records in a table which a field named as 'validity' contains date as '8/15/1947 12:00:00 AM'. I need to set this date as null. I am having 1000 records,in that validity field is '8/15/1947 12:00:00 AM'. I wrote a syntax 

update ACCESSCODE set VALIDITY=null where VALIDITY='8/15/1947 12:00:00 AM';

which throws error as:

'Incorrect date value: '8/15/1947 12:00:00 AM' for column 'VALIDITY' at row 1'.

Here is my table skeleton.

CREATE TABLE `ACCESSCODE` 
(  `SNO` int(11) NOT NULL AUTO_INCREMENT,   
   `ACCESS_CODE` varchar(255) DEFAULT NULL,   
   `STATUS` int(11) DEFAULT NULL,
   `VALIDITY` date DEFAULT NULL,
    PRIMARY KEY (`SNO`) 
) ENGINE=MyISAM 
  AUTO_INCREMENT=10103
  DEFAULT CHARSET=latin1 ;

Please advice and correct me with the syntax.My table name is ACCESSCODE. Am working this TOAD. Thanks in advance.

Best Answer

Use one of the suggested formats in MySQL documentation, for datetime literals, like the 'YYYY-MM-DD HH:MM:SS' format:

WHERE validity = '1947-05-18 00:00:00' ;

or the simpler (in this case, that there is no time part):

WHERE validity = '1947-05-18' ;
Related Question