Board index » delphi » How do you run a date range query in the BDE against a Paradox table

How do you run a date range query in the BDE against a Paradox table

My Win environment and BDE are both set to the following format: xx/xx/xxxx.
When I use the QBE for Paradox I can enter 02/../.. and get all dates in a
table for the month of February.  This is great but what I really need to do
is produce the actual SQL syntax for a query component in a Delphi app.
Does anyone have an idea of how to structure such a statement.  The BDE and
a Paradox manual was no help.

select * from table where bdate like "02/../.."  of course returns a type
mismatch like everything else I have tried from the BDE.

Thanks,

STeve

 

Re:How do you run a date range query in the BDE against a Paradox table


This does pretty much what you want to do.  I just sucked it outa
my latest...  The DTGin & Out are Timestamp fields in the table.
I'm assuming you're using a Timestamp field in P'dox?  I hope?
EndDate and StartDate are TDateTimes from the app.
**********************************
try
  QR1 := TQuery.Create(ReportForm);
  QR1.SessionName := ISMainForm.Session1.SessionName;
  QR1.DatabaseName := 'MSLDB';
  QR1.SQL.Clear;
  QR1.SQL.Add('SELECT CCSD, DTGOut, DTGIn FROM ClosedTickets.DB
WHERE ');
  QR1.SQL.Add('CHARGED IN ('+StaReport.Text+') ');
  QR1.SQL.Add('AND DTGOut >= :OutDTG ');
  QR1.SQL.Add('AND DTGIn <= :InDTG ');
  IF NOT QR1.Prepared THEN QR1.Prepare;
  QR1.Params.ParamByName('InDTG').AsDateTime := EndDate;
  QR1.Params.ParamByName('OutDTG').AsDateTime := StartDate;
  QR1.Open;
*********************************

--
Daniel J. Wojcik
woj...@liaeur.21taacom..army.mil

Steve <srhi...@otn.net> wrote in article
<69s9bk$g...@forums.borland.com>...

Quote
> My Win environment and BDE are both set to the following
format: xx/xx/xxxx.
> When I use the QBE for Paradox I can enter 02/../.. and get all
dates in a
> table for the month of February.  This is great but what I
really need to do
> is produce the actual SQL syntax for a query component in a
Delphi app.
> Does anyone have an idea of how to structure such a statement.
The BDE and
> a Paradox manual was no help.

> select * from table where bdate like "02/../.."  of course
returns a type
> mismatch like everything else I have tried from the BDE.

> Thanks,

> STeve

Re:How do you run a date range query in the BDE against a Paradox table


SELECT * FROM Orders
WHERE EXTRACT(MONTH FROM ShipDate) = 5

Bill

(Sorry but TeamB cannot answer support questions received via email.)
(To send me email for any other reason remove .nospam from my address.)

Re:How do you run a date range query in the BDE against a Paradox table


Quote
On Sat, 17 Jan 1998 23:08:16 -0800, "Steve" <srhi...@otn.net> wrote:
>My Win environment and BDE are both set to the following format: xx/xx/xxxx.
>When I use the QBE for Paradox I can enter 02/../.. and get all dates in a
>table for the month of February.  This is great but what I really need to do
>is produce the actual SQL syntax for a query component in a Delphi app.
>Does anyone have an idea of how to structure such a statement.  The BDE and
>a Paradox manual was no help.

>select * from table where bdate like "02/../.."  of course returns a type
>mismatch like everything else I have tried from the BDE.

There are a couple ways to do what you describe. One is to use the BETWEEN
predicate in the SELECT query, and specify the beginning and ending date
values that comprise the range.

  SELECT *
  FROM YOURTABLE T
  WHERE (T.DATEFIELD BETWEEN "2/1/1997" AND "2/28/1997")

The other way is to use the SQL function EXTRACT to extract the month field
from a date column value. While the SQL statement above would only retrieve
rows where the date is in February of 1997, that below using this method
would retrieve rows for February for all years in the table.

  SELECT *
  FROM YOURTABLE T
  WHERE (EXTRACT(MONTH FROM T.DATEFIELD) = 2)

To limit the above to those rows for only a single year, add another
predicate comparison using EXTRACT to get the year field.

  SELECT *
  FROM YOURTABLE T
  WHERE (EXTRACT(MONTH FROM T.DATEFIELD) = 2) AND
    (EXTRACT(YEAR FROM T.DATEFIELD) = 1997)

**************************************************************************
Steve Koterski
Borland International, Inc.
http://www.borland.com/delphi
(Remove the "SPICEDHAM2" from the address. Death to spam-bots!)

Other Threads