Search code examples
mysqlmysql-workbenchsqldatatypescolumn-defaults

inner join two datasets but return nothing without any error (date format issue)?

I'm new to SQL, currently I'm doing a task about join two datasets, one of the dataset was created by myself, here's the query I used:

USE `abcde`;
CREATE TABLE `test_01`( 
`ID` varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`NUMBER01` bigint(20) NOT NULL DEFAULT '0',
`NUMBER02` bigint(20) NOT NULL, 
`date01` date DEFAULT NULL, 
PRIMARY KEY (`ID`, `date01`))

Then I load the data from a csv file to this table, the csv file looks like this:

ID       NUMBER01    NUMBER02    DATE01
aaa=ee   12345678    235896578   **2009-01-01T00:00:00**

If I query this newly-created table, it looks like this(the format of the 'DATE01' changes):

ID       NUMBER01    NUMBER02    DATE01
aaa=ee   12345678    235896578   **2009-01-01**

Another dataset, I queried and exported to a csv file, the format of the date01 column is like 01/12/1979 and in SQL the format looks like 1979-12-01.

I also usedselect * from information_schema.columns to check the datatype of the columns I need to join, for the newly-created dataset: enter image description here

The date column for another dataset is:enter image description here The differences are: 1. The format of the date column in csv appears different 2. The COLUMN_DEFAULT are different, one is 0000-00-00, another one is NULL.

I wonder the reason why I got empty output is probably because the difference in the 'date' format, but I'm not sure how to make them the same so that I can get something in the output, can someone gave me some hint? Thank you.

Solution

  • the format of the 'DATE01' changes

    Of course, DATE datatype does not contain timezone info/component.

    I wonder the reason why I got empty output is probably because the difference in the 'date' format

    If input value have some disadvantage (like wrong data format) than according value is truncated or is set to NULL. See - you must obtain a bunch of warnings during the importing similar to "truncate incorrect value".

    If the date field in CSV have wrong format then you must use intermediate user-defined variable for accepting raw value, and apply proper converting expression to it in SET clause. Like

    LOAD DATA INFILE ...
INTO TABLE tablename (field1, ..., @date01)
SET date01 = STR_TO_DATE(@date01, '%d/%m/%Y');