Working with Date data can be very tricky. I recently encountered an "
Invalid argument to date encode", error while trying to update a SQLite database table.
This placed a value of
0000-00-00 into the date field of my SQLite table.
Here is the original code which caused the error.
function TForm1.GetOneOffDateAsDate: TDate; begin Result := DateEdit1.Date; end; procedure TForm1.SQLInsertPayment; begin qryO.SQL.Clear; qryO.SQL.Add( ' INSERT INTO OneOffPayments '); qryO.SQL.Add( ' ( '); qryO.SQL.Add( ' OneOffDate, '); qryO.SQL.Add( ' OneOffPayment '); qryO.SQL.Add( ' ) '); qryO.SQL.Add( ' VALUES ( '); qryO.SQL.Add( ' :ood, '); qryO.SQL.Add( ' :
oop '); qryO.SQL.Add( ' ); '); qryO.ParamByName( 'ood' ).Value := GetOneOffDateAsDate; qryO.ParamByName( '
oop' ).Value := GetOneOffAmount; qryO.ExecSQL; end;
The getter function
GetOneOffDateAsDate passes in a TDate which doesn't play nicely with FireDAC. Fortunately, the fix is quite simple. I found a fantastic explanation for this error on
stackoverflow which states FireDAC expects DATE data type values to be a string in the fixed format of YYYY-MM-DD.
FireDAC Expects DATE data types
to be strings formatted as YYYY-MM-DD
So I created another getter function to format the date data as a YYYY-MM-DD string.
Problem solved!
Updated code passing FireDAC a YYYY-MM-DD string
function TForm1.GetOneOffDateAs_YYYYMMDD: String; begin Result := FormatDateTime('YYYY-MM-DD', DateEdit1.Date); end; procedure TForm1.SQLInsertPayment; begin qryO.SQL.Clear; qryO.SQL.Add( ' INSERT INTO OneOffPayments '); qryO.SQL.Add( ' ( '); qryO.SQL.Add( ' OneOffDate, '); qryO.SQL.Add( ' OneOffPayment '); qryO.SQL.Add( ' ) '); qryO.SQL.Add( ' VALUES ( '); qryO.SQL.Add( ' :ood, '); qryO.SQL.Add( ' :
oop '); qryO.SQL.Add( ' ); '); qryO.ParamByName( 'ood' ).Value := GetOneOffDateAs_YYYYMMDD; qryO.ParamByName( '
oop' ).Value := GetOneOffAmount; qryO.ExecSQL; end;
Happy coding!
Enjoy!
Gunny Mike
https://zilchworks.com
Weiterlesen...