CODEWITHSUNDEEP
delphidelphi-xe2sybase-asa
Sharpie
Sharpie

Reputation: 383

Filter database records by Date range

I have a database system which allows users to create records. At the point of posting the record to the table it captures the current date and time.

I have a form which allows users to filter/search for records.

What is the best way to allow filtering for the Dates of the records. Like StartDate an EndDate?

The below is what I do when capturing the DateTime for the record.

FieldByName('logged_dt').AsDateTime := now;

Upvotes: 0

Views: 2265

Answers (1)

Ken White
Ken White

Reputation: 125708

The typical way to do so is by using SQL with parameters and a WHERE clause:

Query.SQL.Clear;
Query.SQL.Add('SELECT ColumnA, ColumnB, DateCol');
Query.SQL.Add('FROM TableA');
Query.SQL.Add('WHERE DateCol >= :StartDate');
Query.SQL.Add('AND DateCol <= :EndDate');
Query.ParamByName('StartDate').AsDateTime := StartDateYouWant;
Query.ParamByName('EndDate').AsDateTime := EndDateYouWant;
Query.Open;
if Query.IsEmpty then
  ShowMessage('No records found in that range')
else
  // Do whatever with the rows found

An alternative syntax is to use BETWEEN, which would change the WHERE clause to

Query.SQL.Add('WHERE DateCol BETWEEN :StartDate AND :EndDate');

I've run across issues with using BETWEEN with DATETIME columns because of the time portion of the value, and (in my own experiences) using the >= (GTE) and <= (LTE) operators works more easily. Your mileage may vary, so you should test and decide which works best for you with your particular DBMS and data.

Upvotes: 2

Related Questions