Solved

sql example

Posted on 2001-07-30
16
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +3
16 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6334378
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
ID: 6334407
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
ID: 6334520
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 6334646
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
ID: 6334663
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
ID: 6334694
yup, so is it, J. :-)
0
 
LVL 2

Expert Comment

by:FrodoBeggins
ID: 6336721
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
ID: 6336756
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
 
LVL 12

Expert Comment

by:Faruk Onder Yerli
ID: 6337291
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
ID: 6337337
? 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
ID: 6339226
I would prefer using parameters for this example. It suits for this application better.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6339353
as it is :-(
0
 
LVL 12

Expert Comment

by:Faruk Onder Yerli
ID: 6339828
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
ID: 6340159
>but if you use ADO comp. then You have to use my advice.
also ado provides paramaters
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6343674
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
ID: 6345710
not at all, j.
you gave a bit more explaination

its ok so

meikl ;-)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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