Member Avatar for sathish_nadu

Im using LOAD DATA INFILE to import a csv file, the files date format is 29/11/2010 and the database format is 2010-11-29, what can i use to format the date inside the query?

I've tried str_to_date:

SET date_start = STR_TO_DATE(@from_date,'%Y-%m-%d'),
but that only inserts 0000-00-00

  • 2 Contributors
  • 1 Reply
  • 220 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by cereal
Member Avatar for cereal

Change it to:

SET @date_start = DATE_FORMAT(STR_TO_DATE(@from_date,'%d/%m/%Y'), '%Y-%m-%d');

Full example:

> SET @from_date = '29/11/2010'; SET @date_start = DATE_FORMAT(STR_TO_DATE(@from_date, '%d/%m/%Y'), '%Y-%m-%d'); show warnings; select @date_start;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Empty set (0.00 sec)

+-------------+
| @date_start |
+-------------+
| 2010-11-29  |
+-------------+
1 row in set (0.00 sec)

Note: if you get warnings restart the client, it could be that a variable remains in memory.

Edited by cereal
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.