SQL Insert and Dates
lazarus / 16-Databases

----------------------------------------------------------------------
gkelby · 2014-04-11 09:28

Hello:

I was hoping someone could help me with this problem.  Essentially I
can insert data to a database table using SQLite3 however when
attempting to insert date values nothing happens.  No errors are
reported and all the data that is not a date is inserted into the
table.
I am using:
Lazarus version 1.1.99pre-release, FPC version 2.6.2, SVN version
42829, i386-win32-win32/win64
SQLite3 and Windows 7 Ultimate 64.

The following are the relevant code samples.

var
  fmPersonalProfiles: TfmPersonalProfiles;

  CurrentYear: Integer;
  StartFY: TDate;
  EndFY: TDate;
  CreatedToday: TDate;
  UpdatedToday: TDate;
 
//------------------------------------------
  //YearComboBox.Text is a data entry field where the user specifies a
year such as 2013, 2014, 2015, etc.
 
  CurrentYear := StrToInt(YearComboBox.Text)-1;  //CurrentYear is the
year selected less one.

  StartFY := StrTodate(FormatDateTime('dd/mm/yyyy', StrtoDate('1/7/'+
IntToStr(CurrentYear))));
  EndFY := StrTodate(FormatDateTime('dd/mm/yyyy', StrtoDate('30/06/'+
AnsiString(YearComboBox.Text))));
  CreatedToday := StrTodate(FormatDateTime('dd/mm/yyyy', Now));
  UpdatedToday := StrTodate(FormatDateTime('dd/mm/yyyy', Now));

//Having done a ShowMessage on each of the values they all look
correct.
 
//This is the Insert SQL command

  DM2.SQLQuery1.SQL.Text := 'Insert Into Profile' +
  '(ProfileName, ProfileYear, TaxJurisdiction, StartDate, EndDate,
CreatedDate, UpdatedDate)' +
  'Values (:ProfileName, :ProfileYear, :Jurisdiction, :StartDate,
:EndDate, :CreatedDate, :UpdatedDate)';

//DM2 is a data module containing the data base connections and data
access connections.  It all works.

//These SQL commands work fine...they populate the respective database
table fields.
  DM2.SQLQuery1.Params.ParamByName('ProfileName').AsString:=AnsiString
(NewName.Text);
  DM2.SQLQuery1.Params.ParamByName ('ProfileYear').AsInteger :=
StrToInt(YearComboBox.Text);
  DM2.SQLQuery1.Params.ParamByName ('Jurisdiction').AsString :=
AnsiString(Jurisdiction1.Text);
 
//These SQL commands do not work.  That is, the database table fields
are empty, while the data fields populated by the three statements
above are.
  DM2.SQLQuery1.Params.ParamByName ('StartDate').AsDate := StartFY;
  DM2.SQLQuery1.Params.ParamByName ('EndDate').AsDate := EndFY;
  DM2.SQLQuery1.Params.ParamByName ('CreatedDate').AsDate :=
CreatedToday;
  DM2.SQLQuery1.Params.ParamByName ('UpdatedDate').AsDate :=
UpdatedToday;

Any assistance would be greatly appreciated.
Thanks.
GK

----------------------------------------------------------------------
LacaK · 2014-04-11 09:54

Do you use sqlDB TSQLite3Connection ? or other component?

----------------------------------------------------------------------
mangakissa · 2014-04-11 11:23

It should be working, but try .AsDateTime insead of .AsDate

----------------------------------------------------------------------
kpeters58 · 2014-04-11 17:43

Seems to me you should read up on this:

http://www.sqlite.org/datatype3.html

The absence of a date type makes many people stumble...

I always store my dates as strings in SQLite tables by passing them to
a function like this (simplest form):

Code: [Select]function DBDateStr(const aDate: TDate): String;
begin
  // date should be ISO 8601 compliant
  Result :=  QuotedStr(FormatDateTime( 'yyyy-mm-dd', aDate));
end;
and then store the result in the DB (via parameter or otherwise)

----------------------------------------------------------------------
JZS · 2014-04-12 13:10

Had several collisions with STRING<=>DATE,
Arrived to one decision, unless you are the only one using it for
short time, never attempt to format the date, the way you did:

like:
UpdatedToday := StrTodate(FormatDateTime('dd/mm/yyyy', Now));
Worse:
StartFY := StrTodate(FormatDateTime('dd/mm/yyyy', StrtoDate('1/7/'+
IntToStr(CurrentYear))));

For the first one use:
DM2.SQLQuery1.Params.ParamByName ('UpdatedDate').AsDate:=Now();// or
just :=Date;

For the second one use:
StartFY:=encodedate(year, month, day); //substitute as you see fits,
in your case could be: encodedate(CurrentYear, 1, 7);

Regards,

----------------------------------------------------------------------
gkelby · 2014-04-15 06:58

Thanks to all who wrote in and provided suggestions.  I followed up
two of them: kpeters58 suggestion to read up on SQLite3 datatypes and
JZS's suggestion about using strings and the "encodedate procedure.  I
couldn't find any documentation on it however, I did try his
suggestion and the results didn't appear to be any different than
previous.  However, I found using "TEXT" and converting back 'n' forth
seemed to work.  I was able to populate the "Date" fields with values,
albeit as Text though.  Not really what I wanted, but it seems to
work.

Thanks for your help.
GK.

----------------------------------------------------------------------
eara · 2014-04-17 06:38

in a previous attempt at SQLite i used DATETIME fields in db (If this
fits in your needs). Then i access them with
FieldByName('x').AsDateTime:= myDateTimeVariable;