Sequel Pro: How to change a string to a date during import?

Question

I'm trying to import a CSV file into a MySQL table using Sequel Pro.

I know I need to use STR_TO_DATE, but I can't figure out the right syntax. I've tried this syntax STR_TO_DATE(@$1, '%d/%m/%y') which is not working.

The CSV file format

Date Created, Date Modified
07/10/16,02/02/17,
07/10/16,,
07/10/16,,
07/10/16,,
07/10/16,23/01/17,

Table schema

CREATE TABLE `date` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Date Created` date NOT NULL,
  `Date Updated` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

---

---

1. File > Import. The file comes up and the date field in the CSV is row 1:

2. Select Date > Add expression

3. Final import view

4. Error

The expression is returning STR_TO_DATE(@$1, '%d/%m/%y') NULL but I don't know why?

Answer 1

A small change to the expression solved the problem.

Instead of just converting the string into date I added a check to find empty string.

IF( $2='', null, STR_TO_DATE( $2, '%d/%m/%Y' ) )