• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

sql example

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
eebeginer
Asked:
eebeginer
  • 7
  • 3
  • 2
  • +3
1 Solution
 
kretzschmarCommented:
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
 
jswebyCommented:
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
 
SMakarenkoCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
kretzschmarCommented:
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
 
jswebyCommented:
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
 
kretzschmarCommented:
yup, so is it, J. :-)
0
 
FrodoBegginsCommented:
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
 
SMakarenkoCommented:
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
 
Faruk Onder YerliOwnerCommented:
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
 
kretzschmarCommented:
? 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
 
eebeginerAuthor Commented:
I would prefer using parameters for this example. It suits for this application better.
0
 
kretzschmarCommented:
as it is :-(
0
 
Faruk Onder YerliOwnerCommented:
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
 
kretzschmarCommented:
>but if you use ADO comp. then You have to use my advice.
also ado provides paramaters
0
 
jswebyCommented:
I think kretzschmar came up with this solution, maybe you should offer him some points for this too.

J.
0
 
kretzschmarCommented:
not at all, j.
you gave a bit more explaination

its ok so

meikl ;-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now