How can I change the MySQL date format?

Question

I am running a local MariaDB server via XAMPP. I have ~1000 LOC, which look like the following:

insert into jobs values(781,'13-01-2005',10006,1,2.5,1,4.00);

As I am using a european date format I keep getting the error:

Data truncation: Incorrect date value: '13-01-2005'

Is there any possibility to modify the date format, the database is using so that it will accept 'DD-MM-YYYY' as an input?

Thanks in advance

The format is static, check here: http://stackoverflow.com/questions/4052862/how-to-change-mysql-date-format-for-database — Jeff Clayton, Jan 11 '17 at 19:35

score 1 · Answer 1 · answered Jan 11 '17 at 19:59

You can convert it with a little trick.

1) Create a temp. Table like this, where the date is stored as varchar

    CREATE TABLE `dateconv` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `datestr` varchar(32) DEFAULT NULL,
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  `i3` double DEFAULT NULL,
  `i4` int(11) DEFAULT NULL,
  `i5` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=782 DEFAULT CHARSET=utf8;

2) Insert all your data

insert into dateconv values(781,'13-01-2005',10006,1,2.5,1,4.00);
...
insert into dateconv values(999,'13-01-2005',10006,1,2.5,1,4.00);

3) you can select or insert the data in your destination table

to show:

SELECT 
    id, STR_TO_DATE(datestr, "%d-%m-%Y"),
    i1,i2,i3,i4,i5
FROM dateconv;

to insert:

INSERT INTO jobs
SELECT 
    id, STR_TO_DATE(datestr, "%d-%m-%Y"),
    i1,i2,i3,i4,i5
FROM dateconv;

alternativ way

turn a function STR_TO_DATE around each datestring like this:

insert into jobs values(781,STR_TO_DATE('13-01-2005', "%d-%m-%Y"),10006,1,2.5,1,4.00);

How can I change the MySQL date format?

1 Answers1