Solved

sql example

Posted on 2001-07-30
16
259 Views
Last Modified: 2010-04-06
I want to filter all records between date values entered in two edit boxes and dynamically create an sql statement at runtime, letting the user select the date values.

example will be appriciated.
0
Comment
Question by:eebeginer
  • 7
  • 3
  • 2
  • +3
16 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
prepare your tquery-sql text like

select * from atable where ADate between :ABegin and :AEnd

(whereas ATable should be the real name of the table and ADate should be the real fieldname)

and use this coed

query1.close;
query1.parambyname('ABegin'),AsDateTime := StrToDateTime(Edit1.text);
query1.parambyname('AEnd'),AsDateTime := StrToDateTime(Edit2.text);
query1.Open;

meikl ;-)

0
 
LVL 4

Accepted Solution

by:
jsweby earned 50 total points
Comment Utility
Meikl is spot on here, as usual. If you plan to use this query more than a couple of times, you can improve its speed by PREPARING the query before running it. e.g.

In the FormActivate or somewhere that is called when you reach the screen you want:

Query1.SQL.Clear;
Query1.SQL.Append('select * from atable where ADate between :ABegin and :AEnd');
Query1.SQL.Prepare;

When the user presses an OK button (or whatever prompts the launch of the query):

query1.close;
query1.parambyname('ABegin'),AsDateTime := StrToDateTime(Edit1.text);
query1.parambyname('AEnd'),AsDateTime := StrToDateTime(Edit2.text);
query1.Open;

J.
0
 

Expert Comment

by:SMakarenko
Comment Utility
you may use next text :

 

procedure TForm1.Edit1Change(Sender: TObject);
begin
  Timer1.Enabled:=False;
  Timer1.Enabled:=True;
end;

procedure TForm1.Edit2Change(Sender: TObject);
begin
  Timer1.Enabled:=False;
  Timer1.Enabled:=True;
end;


procedure TForm1.Timer1Timer(Sender: TObject);
begin
  Timer1.Enabled:=False;
  if Query1.Active then Query1.Close;
  Query1.SQL.Clear;
  Query1.SQL.Text:='select * from table_name where        data   between  '''+Edit1.Text+''' and '''+Edit2.Text+'''';
  Query1.Open;
  ...

end;

0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
there is a typo:
query1.parambyname('ABegin'),AsDateTime
query1.parambyname('AEnd'),AsDateTime
should be
query1.parambyname('ABegin').AsDateTime
query1.parambyname('AEnd').AsDateTime
(,->.)

to SMakarenko,

keep in mind that the dateformat on the server could be different to the dateformat at the client

meikl ;-)
0
 
LVL 4

Expert Comment

by:jsweby
Comment Utility
That is EXACTLY why I always use parameters for passing dates in a query, even for the simplest query, because I have found that using the Parameter AsDateTime way always formats the date the way the server wants it.

J.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
yup, so is it, J. :-)
0
 
LVL 2

Expert Comment

by:FrodoBeggins
Comment Utility
In fact, AsDateTime does not format the date the right way. It just sends the date as a variable of type 'Double', where the integer part is the number of days passed after Dec/31/1899 and the fraction is the time.

That's why I never use text for dates. It is not only the date format that bothers me. Sending a text is like converting a DateTime to string, sending the string (much more then just a Double) and converting the string back to DateTime. And every conversion may cause an error :)

Rgds,
Frodo
0
 

Expert Comment

by:SMakarenko
Comment Utility
to kretzschmar  agree :)
(simply i use Oracle and text above suit for me)


for Date filter:
 

if Query1.Active then Query1.Close;
Query1.SqlText:=
   'Select * from Table_name where
      date_Field  between :BEGIN_DATE and :FINISH_DATE ';
Query1.ParamByName('BEGIN_DATE').AsDateTime:=
        Trunc(DateTimePicker1.Date);
Query1.ParamByName('FINISH_DATE').AsDateTime:=
        Trunc(DateTimePicker2.Date);
 

for DateTime filter:

use MaskEdit

(
 MaskEdit1.Editmask:='!99/99/0000 !90:00;1;_';  
 MaskEdit2.Editmask:='!99/99/0000 !90:00;1;_';
 )


Query1.ParamByName('BEGIN_DATE').AsDateTime:=
        StrToDateTime(MaskEdit1.Text);
Query1.ParamByName('FINISH_DATE').AsDateTime:=
        StrToDateTime(MaskEdit1.Text);




 

 

 

 
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 12

Expert Comment

by:Faruk Onder Yerli
Comment Utility
hi eebeginer ;

First You must fix DateTime Format.
exmp:
  ShortDateFormat := 'dd.mm.yyyy';
  LongTimeFormat  := 'HH:mm:ss';
  ShortTimeFormat := 'HH:mm:ss';
  DateSeparator := '.';

then you can date string in SQL like that :

var
  tmpStr : string;
begin
  tmpStr := SQLDateFormat(Now);
...
  SQL.Add('Where RecDate=''' + tmpStr + '''');
...
end;

function SQLDateFormat(Value: TDateTime): String;
var
  ss,dd,sn,ms,
  y, a, g: Word;
begin
  DecodeDate (Value, y, a, g);
  DecodeTime (Value, ss, dd, sn,ms);
  Result := Format('%d.%d.%d %d:%d:%d',[y,a,g,ss,dd,sn]);
end;

This method is reliable so much. You can assign everything tmpstr. Allofthem  run..
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
? how does you mean that your answer is correct

let say, i'm a client running the european dateformot
and the server is american dateformat (even the client don't know this

now your answer generates a statement like

select * from atable where adate = '2001.07.31 14:04:00'

whereas the server wants to have

select * from atable where adate = '07/31/2001 02:04:00 PM'

with your method i will get an error like invalid dateformat

its really more safe to use parameters for datefields

meikl ;-)

eebeginer, please reject equalizer's proposed answer
0
 

Author Comment

by:eebeginer
Comment Utility
I would prefer using parameters for this example. It suits for this application better.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
as it is :-(
0
 
LVL 12

Expert Comment

by:Faruk Onder Yerli
Comment Utility
where ever you want to use this string, You have to convert to SQL Date Format. Because, when a computer date format changes, your program doesnt run.

if SQL server Date Format is  'dd/mm/yyyy' then your computer date format have to same.

** if you use BDE comp. then BDE will convert format by auto.
but if you use ADO comp. then You have to use my advice.

good luck...
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>but if you use ADO comp. then You have to use my advice.
also ado provides paramaters
0
 
LVL 4

Expert Comment

by:jsweby
Comment Utility
I think kretzschmar came up with this solution, maybe you should offer him some points for this too.

J.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
not at all, j.
you gave a bit more explaination

its ok so

meikl ;-)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now