Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql example

Posted on 2001-07-30
16
Medium Priority
?
268 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 200 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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 The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

721 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