Delphi Tip of the Day: Prevent SQLite Date Headaches by using a GetDateAs_YYYYMMDD Function

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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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