Database and DateTime Fields
lazarus / 16-Databases

----------------------------------------------------------------------
Pierre.Marthinet · 2016-02-23 21:54

Hi !
I would like to store data in DateTime format in a database and I
think to use ftDateTime in my FieldDefs.
My problem is : how can I write an EncodeDateTime in this Field ?
Many thanks !!

----------------------------------------------------------------------
Jurassic Pork · 2016-02-23 23:17

hello,
if you use a TSqlQuery component , you can try something like that :

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.FieldByName('MyDateField').AsDateTime :=
EncodeDate(2016,2,21);
Friendly, J.P

----------------------------------------------------------------------
Pierre.Marthinet · 2016-02-24 06:17

Hello Jurassic !

Many thanks !
And if I use a TDbf component ?

Friendly, PM

----------------------------------------------------------------------
mangakissa · 2016-02-24 08:20

TDBF is inherited from Tdataset just like TSQLquery. FieldByName() is
a property of TDataset.

----------------------------------------------------------------------
SymbolicFrank · 2016-02-24 08:30

I use this:

Code: [Select]function ISODate(ThisDateTime: TDateTime): string;
begin
  Result := FormatDateTime('YYYY-MM-DD', ThisDateTime);
end;

function ISODateTime(ThisDateTime: string): TDateTime;
var
  s: string;
  Year, Month, Day: Integer;
  Hour, Min, Sec: Integer;
begin
  Result := 0;

  s := Trim(ThisDateTime);

  if (Length(s) < 10) or (Pos('-', s) <> 5) then Exit;

  try
    Year := StrToInt(Copy(s, 1, 4));
    Month := StrToInt(Copy(s, 6, 2));
    Day := StrToInt(Copy(s, 9, 2));

    if Length(s) >= 19 then
    begin
      Hour := StrToInt(Copy(s, 12, 2));
      Min := StrToInt(Copy(s, 15, 2));
      Sec := StrToInt(Copy(s, 18, 2));

      Result := EncodeDateTime(Year, Month, Day, Hour, Min, Sec, 0);
    end
    else
    begin
      Result := EncodeDate(Year, Month, Day);
    end;
  except
    //
  end;
end;

and I insert it in the query:

Code: [Select]MyQuery.SQL.Text := 'insert into datetable (startdate)
';
MyQuery.SQL.Add('values (''' + ISODateTime(ThisDate) + ''');');

Edit: corrected some errors.

----------------------------------------------------------------------
Pierre.Marthinet · 2016-02-24 21:41

Hi all !

Many thanks for your help !
All is working fine but I have a doubt about the real content of my
DateTime Field :

Dbf2.Edit;
TheDate :=  (EncodeDateTime(2016, StrToInt(DBEdit10.Text), StrToInt(DB
Edit9.Text),StrToInt(DBEdit8.Text),StrToInt(DBEdit7.Text),0,0)) ;
Dbf2.FieldByName('DateTime').AsDateTime := TheDate;
Edit2.Text :=  FormatDateTime('c' ,TheDate);
Dbf2.Post;      

Edit2.Text shows the entire date and time (exemple 24/02/2016
22:35:00)
But DBEdit11 who is linked with DateTime Datafield shows only
24/02/2016...not the time....
(Sorry for my certainly basic questions but this is my first
experience with LAZARUS and Pascal programming....)

----------------------------------------------------------------------
Jurassic Pork · 2016-02-24 22:55

hello,
with this code to create and populate a dbf database :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button2Click(Sender: TObject);var  MyDbf: TDbf;  tDT :
TDateTime;  ss : Word;begin  MyDbf := TDbf.Create(nil);  try    { make
sure the data directory exists - otherwise this may fail (e.g. on
Windows XP): }    ForceDirectories('data');    { use relative path to
"data" directory }    MyDbf.FilePath := 'data' + DirectorySeparator; 
 { we want to use Visual dBase VII compatible tables }   
MyDbf.TableLevel := 7;    MyDbf.DateTimeHandling:= dtDateTime;   
MyDbf.Exclusive := True;    MyDbf.TableName := 'customers.dbf';   
With MyDbf.FieldDefs do begin      Add('AutoId', ftAutoInc, 0, True); 
    Add('Name', ftString, 10, True);      Add('Datetime',
ftDateTime,0,False );    End;    MyDbf.CreateTable;    MyDbf.Open;   
MyDbf.AddIndex('custid', 'AutoId', [ixPrimary, ixUnique]);    for ss:=
1 to 10 do      begin      MyDbf.Insert;      tDT:=
EncodeDate(2016,2,25)+EncodeTime(12,0,ss,0);     
MyDbf.FieldByName('Datetime').AsDateTime:= tDT;     
MyDbf.FieldByName('Name').AsString := 'Nom' + inttostr(ss);     
end;     MyDbf.Close;  finally    MyDbf.Free;  end;end; 
i get the attachment picture ( tdbgrid and tdbdateEdit components ) .

 DbDateEdit component DateFormat property : yyyy-mm-dd hh:nn

Friendly, J.P

----------------------------------------------------------------------
mangakissa · 2016-02-25 08:42

Quote from: Pierre.Marthinet on February 24, 2016, 10:41:45 pmHi all !

Many thanks for your help !
All is working fine but I have a doubt about the real content of my
DateTime Field :

Dbf2.Edit;
TheDate :=  (EncodeDateTime(2016, StrToInt(DBEdit10.Text), StrToInt(DB
Edit9.Text),StrToInt(DBEdit8.Text),StrToInt(DBEdit7.Text),0,0)) ;
Dbf2.FieldByName('DateTime').AsDateTime := TheDate;
Edit2.Text :=  FormatDateTime('c' ,TheDate);
Dbf2.Post;      

Edit2.Text shows the entire date and time (exemple 24/02/2016
22:35:00)
But DBEdit11 who is linked with DateTime Datafield shows only
24/02/2016...not the time....
(Sorry for my certainly basic questions but this is my first
experience with LAZARUS and Pascal programming....)
        
If you only need a datevalue, why using encodedatetime?
If you're using DBEdits, consider this:
Use encodedate instead of encodedatetimeUse directly fieldnames
instead of DBEditTest if your date is validExample:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var mydate
: Tdate;begin  try  mydate :=
encodedate(Dbf2.FieldByName('year').AsInteger,                     
 Dbf2.FieldByName('month').AsInteger,                     
 Dbf2.FieldByName('day').AsInteger);  Dbf2.edit; 
Dbf2.FieldByName('firstdate').AsDateTime := mydate; 
Dbf2.FieldByName('seconddate').AsDateTime := mydate;  Dbf2.post; 
except    on E : Exception do    ShowMessage('Error raised, with
message : '+E.Message);  end; I created a demo to see what's happens.

----------------------------------------------------------------------
Pierre.Marthinet · 2016-02-25 19:58

Hello !
Many thanks, it's more clear for me now.
I need DateTime values because I would like to use a Chart with a
TDbChartSource and with the DateTimeIntervalChartSource.
My first trials are hopeful with your help !