Iulian Glăvan Iulian Glăvan - 1 year ago 141
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


declare p, c, v, m, dublare int;
declare dublare_inreg condition for 1062;
declare continue handler for dublare_inreg
set dublare = 0;
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

Answer Source

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')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download