Iulian Glăvan Iulian Glăvan - 1 month ago 8
MySQL Question

str_to_date and concat to unit date and time

str_to_date and concat unite function with time and date. I don't know how to change the format to import or after or populate directly when making visits.

CREATE trigger `trigger` before insert
on `int`
for each row


BEGIN

declare p, c, v, m, dublare int;
declare dublare_inreg condition for 1062;
declare continue handler for dublare_inreg
begin
set dublare = 0;
end;
set dublare = 1;
set foreign_key_checks = 0;



insert into pacienti(nume,prenume) values(new.numepacient,new.prenumepacient);

set p = last_insert_id();
if dublare = 0 then
select distinct id_p from pacienti where nume = new.numepacient into p;
set dublare = 1;

end if;

insert into cabinete(den_cabinet) values(new.cabinet);

set c = last_insert_id();
if dublare = 0 then
select distinct id_c from cabinete where den_cabinet =new.cabinet into c;
set dublare = 1;

end if;

set m = last_insert_id();
if dublare = 0 then
select distinct id_m from medici where id_m = nume into m;
set dublare = 1;
end if;

INSERT INTO vizite(data_ora,id_cabinet,id_pacienti,id_medici)
values(str_to_date(new.datavizita, ' ' , new.oraintrare, '%d / %m / %Y %H:%i:%s')), p , c , m);


In my csv file i have format data 22/02/2016

And this is my load.

load data local infile 'D:\\pfinal\\vizite.txt' IGNORE into table `int`
fields terminated by ','
lines terminated by '\n'
ignore 190000 lines
(DataVizita,OraIntrare,NumePacient,PrenumePacient,NumeMedic,PrenumeMedic,Cabinet)
;

Answer

Remove the spaces in your date format, they dont exist in your input date, and use CONCAT to amalgamate the date and time fields

str_to_date(CONCAT(new.datavizita,' ',new.oraintrare),'%d/%m/%Y %H:%i:%s')