Mysql Query Date (date)


my query:

I have a date field in MySQL that is dated, and at the time of inserting it into the table, it does not accept this format: 'dd / MM / yyyy', because I believe that mysql accepts another type of format.

CREATE TABLE tb_peliculas
(codpeli char(5) primary key not null,
nompeli varchar(50) not null,
genero varchar(40),
fechaestreno date,
duracion varchar(40),
idioma varchar(20),
idsubtitulo int,
idcalidad int,
precio double); 

INSERT INTO TB_PELICULAS VALUES('P0001', 'X-men: Apocalipsis', 'Ciencia Ficción, Acción', '19/05/2016', '2 horas y 10 minutos','Inglés y Español Latino',1,1,50.0);

When I insert the above, I get this error:

Incorrect date value: '19/05/2016' for column 'fechaestreno' at row 10.000 sec

How can I accept the format: dd / MM / yyyy?

asked by Jorge Requez 28.05.2016 в 22:53

2 answers


apparently already that question has been resolved here on the platform ( link ) but I prefer to always handle that type varchar field if you are doing the insertions yourself, I have taken it as experience

answered by 28.05.2016 в 23:11

I would not complicate myself. I think it's easier to save the date as yyyy-mm-dd hh: mm: ss and when you get the date use the programming language as necessary to catch the day, the month etc to reformat it. For example, you could do an explode with php to first separate the date from the time for a space and then separate the elements of the date by making another explode by the scripts and the time separating it by the two points and so you would have each number of the date and the time, then you could create a string by sorting those elements with the order you want and the separators you want. It seems to me a simple method.

answered by 28.05.2016 в 23:40