Format date (from a txt file) to Insert to Mysql

Question

Follows the code:

    //informações necessarias para inserir no DB.
     coo := copy(lTemp,53,6);
                 ccf := copy(lTemp,47,6);
                 ecf := copy(lTemp,4,20);
     //Data sendo formatada para ser inserida
                 dtc1:= StrToDate(copy(lTemp,65,2)+'/'+copy(lTemp,63,2)+'/'+copy(
                   lTemp,59,4));
    //Query para inserção de linha no DB.
    DModuleGrid.ZQuery2.Close;
                 DModuleGrid.ZQuery2.SQL.Clear;
                 DModuleGrid.ZQuery2.SQL.Add('INSERT INTO tabc460(dtcompra, impcaixa, numcupom, ccf, valor) VALUES ( "'+DateTimeToStr(dtc1)+'", "'+ecf+'", "'+coo+'", "'+ccf+'", "'+FloatToStr(valortxt)+'")');
                 DModuleGrid.ZQuery2.ExecSQL;
                 DModuleGrid.ZQuery2.Close;
                 DModuleGrid.ZQuery2.SQL.Clear;
                 DModuleGrid.ZQuery2.SQL.Add('SELECT * FROM tabc460 ORDER BY dtcompra LIMIT 0,'+valor);
                 DModuleGrid.ZQuery2.Open;

Well, what I need is just to get the date format right, because when you format it like this above, it looks really good on Show Message, the two formats are ok dd/mm/yyyy, but when I go to see in the database it appears all scrambled 20/03/2023 rather than 17/03/2014.

What could be wrong?

Answer 1

To solve this problem the ideal is to use typed parameters because they protect the data from wrong conversions, and protect your code against Sqlinjection, therefore:

DModuleGrid.ZQuery2.SQL.Clear;
DModuleGrid.ZQuery2.SQL.Add('INSERT INTO tabc460(dtcompra, impcaixa, numcupom, ccf, valor) VALUES (:dtcompra, :impcaixa, :numcupom, :ccf, :valor)');

DModuleGrid.ZQuery2.ParamByName('dtcompra').AsDateTime := dtc1;
DModuleGrid.ZQuery2.ParamByName('impcaixa').AsString:= ecf;
DModuleGrid.ZQuery2.ParamByName('numcupom').AsString := coo;
DModuleGrid.ZQuery2.ParamByName('ccf').AsString := ccf;
DModuleGrid.ZQuery2.ParamByName('valor').AsFloat:= valortxt;

DModuleGrid.ZQuery2.ExecSQL;

Answer 2

Follow the code I wrote based on @Caputo’s code.

with DModuleGrid.ZQuery2 do
           begin
           //comparação de divergências (Identifica se sao mesmo diferentes e grava os valores no banco de dados)
             coo := copy(lTemp,53,6);
             ccf := copy(lTemp,47,6);
             ecf := copy(lTemp,4,20);

             dtc1:= StrToDateTime(copy(lTemp,65,2)+'/'+copy(lTemp,63,2)+'/'+
             copy(lTemp,59,4));
             //FORMATANDO A DATA!!!!!!!! <<<------------------||||
             dtct := FormatDateTime('yyyy/mm/dd', dtc1);
             valortxt := StrToFloat(copy(lTemp, 109, 14)) / 100;

             DModuleGrid.ZQuery2.Close;
             DModuleGrid.ZQuery2.SQL.Clear;
             DModuleGrid.ZQuery2.SQL.Add('INSERT INTO tabc460(dtcompra, impcaixa, numcupom, ccf, valor) VALUES ( :dtcompra1, :impcaixa2, :numcupom3, :ccf4, :valor5)');
             DModuleGrid.ZQuery2.ParamByName('dtcompra1').AsDateTime := dtct;
             DModuleGrid.ZQuery2.ParamByName('impcaixa2').AsString:= ecf;
             DModuleGrid.ZQuery2.ParamByName('numcupom3').AsString := coo;
             DModuleGrid.ZQuery2.ParamByName('ccf4').AsString := ccf;
             DModuleGrid.ZQuery2.ParamByName('valor5').AsFloat:= valortxt;
             DModuleGrid.ZQuery2.ExecSQL;
             //ShowMessage(DModuleGrid.ZQuery2.FieldByName('dtcompra').AsString);
           end;