Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 ?
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;
  FormatSettings: TFormatSettings;
begin
  FormatSettings.DateSeparator := '-';
  FormatSettings.ShortDateFormat := 'YYYY-MM-DD';
  Result := StrToDate(DateStr, FormatSettings);
//[...]
DateEdit1.Date := ParseDateFromDB(FDQuery1.FieldByName('date_of_change').AsString);
                Thanks, it works fine now ! But now I have reverse problem ... I need save date form DateEdit and this code FDCommand1.ParamByName('date_of_change').Value:= dateedit1.Date;  save 2016-10-16 as 459-09-11
– milenjao
                Oct 16, 2016 at 19:38
                See the documentation for FormatDateTime: delphibasics.co.uk/RTL.asp?Name=formatdatetime
– Wosi
                Oct 16, 2016 at 20:00
                I know about this article, but I need solution which works with any date local format. For example I using europan date coding dd-mm-yyyy but I need my aplication works corectly with any local settings
– milenjao
                Oct 16, 2016 at 20:36
                store the date as a datetime format, not as string and all your problems will be solved...
– whosrdaddy
                Oct 17, 2016 at 8:09
FireDAC uses its own mapping to SQLite data types and adds the DATE pseudo data type for you. So as there is the SINGLE pseudo data type that you can use for storing value of that number box.
So you can create your table by FireDAC like this:
FDQuery.SQL.Text := 'CREATE TABLE MyTable (DateField DATE, SingleField SINGLE)';
FDQuery.ExecSQL;
Then you can insert data:
FDQuery.SQL.Text := 'INSERT INTO MyTable (DateField, SingleField) VALUES (:DateField, :SingleField)';
FDQuery.ParamByName('DateField').AsDate := DateEdit.Date;
FDQuery.ParamByName('SingleField').AsSingle := NumberBox.Value;
FDQuery.ExecSQL;
And read them for example this way:
FDQuery.SQL.Text := 'SELECT DateField, SingleField FROM MyTable';
FDQuery.Open;
DateEdit.Date := DateOf(FDQuery.FieldByName('DateField').AsDateTime);
NumberBox.Value := FDQuery.FieldByName('SingleField').AsSingle;
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.