I use an Oracle DB and access it using FireDAC. I need to add to a select SQL request, two specific dates selected by the user. For that I am using two TDateTimePicker components.
I use DateTimeToStr() to convert the date from TDateTimePicker and build the SQL request like this:
FormRelatorio.FDQuery1.SQL.Add('and(PCPEDC.DTFAT) BETWEEN' +
''''+DatetoSTR(DateTimeInicial.Date)+'''' + 'and' +
''''+DatetoSTR(DateTimeFinal.Date)+'''');
Unfortunately, I get a syntax error from the DB because the DB does not accept nor auto adjust the system from month to numeral, accepting only their acronyms and in English (EX: JAN, FEB, MAR etc ...).
Is there any possibility of changing the result?
That is not a TDateTimePicker issue. It is an issue with how to pass a TDateTime (Delphi data type for date and time) to a SQL query. Currently you built a SQL string and so you must provide yourself the translation from date to string in the format accepted by the DB. This is possible but not the way to do it.
Instead, use a parametrized SQL query and FireDAC will do the work for you:
FormRelatorio.FDQuery1.SQL.Add('and (PCPEDC.DTFAT BETWEEN :InicialDate and :FinaleDate)');
FormRelatorio.FDQuery1.ParamByName('InicialDate ').AsDateTime := DateTimeInicial.Date;
FormRelatorio.FDQuery1.ParamByName('FinaleDate').AsDateTime := DateTimeFinal.Date;
This will correctly work if the columns in the database are correctly defined (You didn't showed the table structure).
If FireDAC doesn't do the correct job, you can use Oracle's TO_DATE function and Delphi FormatDateTime:
FormRelatorio.FDQuery1.SQL.Add('and (PCPEDC.DTFAT BETWEEN TO_DATE(''' + FormatDateTime('DD/MM/YYYY', DateTimeInicial.Date) + ''', 'DD/MM/YYYY') and
TO_DATE(''' + FormatDateTime('DD/MM/YYYY', DateTimeFinal.Date) + ''', 'DD/MM/YYYY'));
Oracle's TO_DATE accept a format DD/MM/YYYY and Delphi's FormatDateTime also. This avoid specifying month name.
Disclaimer: I have no Oracle DB available to check what I wrote. I did it from my head. You've got the idea...