mysql "datetime NOT NULL DEFAULT '1970-01-01' " gets turned to 0000-00-00 00:00:00

sql mysql load-data-infile default-constraint
veccy picture veccy · Sep 8, 2010 · Viewed 15.4k times · Source

I have a column created as 

`date_start` datetime NOT NULL DEFAULT '1970-01-01'

However when I upload data from a CSV file with the LOAD DATA command with a blank entry for date_start the value saved is 0000-00-00 00:00:00 ?

Answer

Greg W picture Greg W · Sep 8, 2010

NULL and 'a blank entry' are not always treated the same in MySql. In the case of the datetime data type, blank entries are automatically converted to 0000-00-00 00:00:00. You can think of this as a form of typecasting, where a blank gets typecast to zero.

In your data file, try replacing all blank dates with the DEFAULT keyword.

Related questions

LOAD DATA LOCAL, How do I skip the first line?

I'm trying to Load a CSV file into my MySQL database, But I would like to skip the first line. I fact It contains the name of my columns and no interesting data. Here is the query I'm using: LOAD …

Read More
PHP - Import CSV file to mysql database Using LOAD DATA INFILE

I have a .csv file data like that Date,Name,Call Type,Number,Duration,Address,PostalCode,City,State,Country,Latitude,Longitude "Sep-18-2013 01:53:45 PM","Unknown","outgoing call",'123456',"0 Secs","null","null","null","null","null",0.0,0.0,,, "Sep-18-2013 01:54:14 PM","Unknown","outgoing …

Read More
How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE

I'm using MySQL's LOAD DATA LOCAL INFILE SQL statement to load data from a CSV file into an existing database table. Here is an example SQL statement: LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table FIELDS TERMINATED BY …

Read More