Solved

sql example

Posted on 2001-07-30
16
262 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
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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 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