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

[DP News-Robot]

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...