Delphi SqLite Date load to TDateEdit error

Question

I using SQlite database im my Firemonkey Android application and there is no native DateTime type.

I storing date as text type

insert command:

insert into table (value,date_of_change)
values (:val,date('now'));

it works fine, date is correct stored, order by date works fine but if I want load this date into TDate edit

query:

select id,value,date_of_change
from table
where id = :MyID

code:

FDQuery1.Close;
FDQuery1.ParamByName('MyID').Value:= myid;
FDQuery1.OpenOrExecute;
FDQuery1.First;

NumberBox1.Value:=FDQuery1.FieldByName('suma').AsFloat;
DateEdit1.Date:=FDQuery1.FieldByName('date_of_change').AsDateTime;

I get error 2016-10-16 is not valid date and time but in Date edit I can see correct date !

Do anybody knows correct solution of this problem ?

Answer 1

Since you store the date as a string FireDAC fails to parse the format properly. You need to change the way the string value in the database column date_of_change is parsed using the correct date format.

So, instead of doing this:

DateEdit1.Date:=FDQuery1.FieldByName('date_of_change').AsDateTime;

You should do this:

function ParseDateFromDB(const DateStr: String): TDateTime;
var
  FormatSettings: TFormatSettings;
begin
  FormatSettings.DateSeparator := '-';
  FormatSettings.ShortDateFormat := 'YYYY-MM-DD';
  Result := StrToDate(DateStr, FormatSettings);
end;

//[...]

DateEdit1.Date := ParseDateFromDB(FDQuery1.FieldByName('date_of_change').AsString);