WHERE clause with DATETIME
lazarus / 16-Databases

----------------------------------------------------------------------
Paulnewyork · 2020-06-11 08:06

Hello,
ich have two forms. On form1 (select) the user type a date and time in
an edit field an press a button. In form2 (test).
The results are shown in a listview. The Problem is that the WHERE
Condition in the SQL Statements is not working.
If i try the Select statement in the SQL Server with the format yyyy-
mm-dd hh:mm:ss  I get the correct result.
If i enter in the edit field the date the   format yyyy-mm-dd
hh:mm:ss, i get the error message: 2020-05-31 is not a valid date
format.

I think i have to convert the stringDateTime to MySQL DateTime fomat?!

Here is my cod of these two forms:


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 unit
select; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs,DB, StdCtrls; type   { TForm7 }   TForm7 =
class(TForm)    Button1: TButton;    Button2: TButton;    Button3:
TButton;    Button4: TButton;    Button5: TButton;    Button6:
TButton;    Edit1: TEdit;    Edit2: TEdit;    Edit3: TEdit;    Edit4:
TEdit;    Edit5: TEdit;    Edit6: TEdit;    Label1: TLabel;    Label2:
TLabel;    Label3: TLabel;    Label4: TLabel;    Label5: TLabel;   
Label6: TLabel;    Label7: TLabel;    Label8: TLabel;    procedure
Button1Click(Sender: TObject);  private   public    end; var  Form7:
TForm7;  time1: TDateTime;  stringDateTime :
string; implementationuses test;  {$R *.lfm} { TForm7 }    procedure
TForm7.Button1(Sender: TObject);begin     stringDatetime :=
Edit1.Text;    time1:= StrtoDateTimes (stringDatetime)    
form8.ShowModal; end;   end.                   unit test; {$mode
objfpc}{$H+} interface uses  Classes, SysUtils, Forms, Controls,
Graphics, Dialogs, ComCtrls,connect,DB, StdCtrls; type   { TForm8 }  
TForm8 = class(TForm)    Label1: TLabel;    Label2: TLabel;   
ListViewTest: TListView;    procedure FormShow(Sender: TObject); 
private   public   end; var  Form8: TForm8;  implementationuses
select;  {$R *.lfm}    { TForm8 }  procedure addDatatoTest
(ID,Delivery :string);  var    item : TListItem;  begin item :=
form8.ListView.Items.Add; item.caption :=
ID; item.Subitems.Add(Delivery);      Form2.connectDB; 
 Form2.SQLQuery1.SQL.Text := 'SELECT * FROM test WHERE
'''+stringDatetime+''' < delivery';Form2.SQLQuery1.Open;    while not
Form2.SQLQuery1.Eof do   begin        variableID  :=
Form2.SQLQuery1.FieldByName('id').AsString;     sringDelivery :=
Form2.SQLQuery1.FieldByName('delivery').AsString;       addDatatoTest
(stringID,stringDelivery);    Form2.SQLQuery1.next; end;

----------------------------------------------------------------------
Rainbow6 · 2020-06-11 18:26

Hi,

I really would recommend to not string-concat SQL statements, but to
use the statement params.

E.g. if you have an SQL statement in your SQLQuery control like this:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   SELECT
....  FROM ....  WHERE ... = :whichDateTime  .... 
You can replace the host variables (preceded by a colon) using the
ParamsByName procedure - and you can select the correct data type:
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.Params.ParamByName('whichDateTime').AsDateTime :=
DateTime1.DateTime;  .... 
This way, you don't have to guess which date format the database
wants.

HTH
Bye
Daniel

----------------------------------------------------------------------
Paulnewyork · 2020-06-11 19:08

I tried this already, but i just can enter a date without time

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
Form2.SQLQuery4.SQL.Text := 'SELECT * FROM test WHERE :sqltime<
delivery';Form2.SQLQuery4.Params.CreateParam(ftDateTime, 'sqltime',
ptInput);Form2.SQLQuery4.ParamByName('sqltime').AsDateTime := zeit1; 

If I do your example with DateTime at the End, i got three of error
messages
('whichDateTime').AsDateTime := DateTime1.DateTime;

3 Errors:
Error: Found declaration: CreateParam(TFieldType;const
AnsiString;TParamType):TParam;
and
Wrong number of parameters specified for call to "createParam"
and
Illegal qualifier

and the DateTime at the end is underlined.

----------------------------------------------------------------------
mm7 · 2020-06-11 19:39

try
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
Form2.SQLQuery4.SQL.Text := 'SELECT * FROM test WHERE @sqltime <
delivery'

----------------------------------------------------------------------
Paulnewyork · 2020-06-11 19:50

I tried  @sqltime

Error: The parameter "sqltime" not found.

----------------------------------------------------------------------
GAN · 2020-06-11 21:44

Quote from: Paulnewyork on June 11, 2020, 09:08:50 pmI tried this
already, but i just can enter a date without time

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
Form2.SQLQuery4.SQL.Text := 'SELECT * FROM test WHERE :sqltime<
delivery';Form2.SQLQuery4.Params.CreateParam(ftDateTime, 'sqltime',
ptInput); //<---- delete
thisForm2.SQLQuery4.ParamByName('sqltime').AsDateTime := zeit1;   

If I do your example with DateTime at the End, i got three of error
messages
('whichDateTime').AsDateTime := DateTime1.DateTime;

3 Errors:
Error: Found declaration: CreateParam(TFieldType;const
AnsiString;TParamType):TParam;
and
Wrong number of parameters specified for call to "createParam"
and
Illegal qualifier

and the DateTime at the end is underlined.

Hi, do not use Params.CreateParam. What is zeit1?

Also:
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
Form2.SQLQuery4.SQL.Text := 'SELECT * FROM test WHERE delivery >
:sqltime ';

----------------------------------------------------------------------
Paulnewyork · 2020-06-12 06:41

Problem is resolved. I can enter now date and time
Form1:
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     time1:=
Edit1.Text;    stringDateTime  := DateTimeToStr(time1); 
Form2:
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  
 Form2.SQLQuery1.SQL.Text := 'SELECT * FROM test WHERE ... > :zeit'; 
 Form2.SQLQuery1.ParamByName('time').AsDateTime := stringDateTime  ;