0

Right now I have a database in the format with columns:

id - purchase_date - integer fields.....

And an import of a CSV using the script

LOAD DATA LOCAL INFILE 'C:/Users/Alex/Desktop/work/natgas2.csv' INTO TABLE pricemodel.natural_gas 
   FIELDS
        TERMINATED BY ',' 
        ENCLOSED BY '"'
        LINES TERMINATED BY '\r\n';

And everything's being imported correctly except for the dates which are in the format right now of: 

1/3/2011

So the question is what format do they have to be in for them to be read correctly into my database?

gcalex5
  • 1,091
  • 2
  • 13
  • 23

1 Answers1

1

The format should be: YYYY-MM-DD, e.g. 2011-01-03.

Here's a way to do it (I'm assuming you're using phpMyAdmin):

  1. Go to the table structure. Change the date column's type to VARCHAR.
  2. Import everything into the database using the SQL you wrote.
  3. Run a query like this: UPDATE your_table SET date = STR_TO_DATE(date, '%m/%d/%Y');
  4. Now go back into the table structure and change the column type back to DATE.
M Miller
  • 5,364
  • 9
  • 43
  • 65
  • right now it's a date – gcalex5 Jul 08 '13 at 16:15
  • 1
    Okay, same thing but without the time part, i.e. it should be in the format of `YYYY-MM-DD` (`2011-01-03`). – M Miller Jul 08 '13 at 16:26
  • If you're using PHP, you can reformat these dates quickly with a function like `$date = strtotime('1/3/2011'); $stamp = date('Y-m-d');`. – M Miller Jul 08 '13 at 16:28
  • Is there a way to do it with the SQL query that I wrote because I'm doing it manually through the workbench not in my program? – gcalex5 Jul 08 '13 at 17:21
  • Hmm, I don't think it would be possible through the SQL itself, but depending on your program, you might be able to find a way. In fact, if it's a CSV file, you could open it in a program like Notepad++ and use a regex find/replace. It'd be rather complicated to write, but it's certainly the right way to go about it, if you have thousands of rows. – M Miller Jul 08 '13 at 22:14
  • 1
    Oh! I know! See my changed answer in a few minutes while I write it.. – M Miller Jul 08 '13 at 22:15
  • that's easier than what I ended up doing. I ended up just bringing it in as a string and converting it in my entity. – gcalex5 Jul 09 '13 at 01:03