Search code examples
mysqldatetime

Datetime Formats

Mysql noobie here. I'm trying to load a csv file that has 2 types of date format: 1 is dd/mm/yyyy and the other is dd/mm/yyyy hh:mm. I gather that mysql has a standard datetime format, which is different to both of these, but that you can use different formats by using a format statement. I've used a suggestion here to use the LOAD DATA syntax to bring it in as user defined variables, and then to fix the format with str_to_date. However I was wondering if that is the standard way? It seems pretty clunky, just to deal with dates in another format.

Solution

  • A pattern:

    LOAD DATA INFILE {filename}
INTO TABLE {tablename}
{options}
( {columns} , @datevalue, {columns} )      -- load custom value into user-defined variable
SET date_column = STR_TO_DATE(@date_value, '%d\/%m\/%Y') -- parse and save into the column

    LOAD DATA Statement # Input Preprocessing