CODEWITHSUNDEEP
delphisortingdelphi-xe
Richard Holland
Richard Holland

Reputation: 2683

Filtering and comparing a date stored as a string in Delphi

Working with a legacy project that has a date stored as a string in the format 

'6/1/2013'

Example of the date range filter I'm trying to do:

 table.filter := 'stringdate >= ' + QuotedStr(adatepicker.text) + ' and ' +
     'stringdate <= ' + QuotedStr(enddatepicker.text);

Obviously this doesn't work. When setting a date range filter the data is incorrect because we're string sorting a date.

What are some ways I can quickly hack this to make it work while planning a later migration to a proper date data type?

Upvotes: 0

Views: 3467

Answers (3)

mr_gian55
mr_gian55

Reputation: 43

on delphi and adotables and .filter property you can code: 

  mytable.filter:='mydatefield=#2018-12-31#';

  mytable.fittered:=true;

Upvotes: 0

Philo
Philo

Reputation: 1989

I think, you can still compare a date stored as a string or varchar, but this is certainly a bad practice because you will need to convert this strings into dates data type to be able to compare between them. If you have indexes defined on the column, they cannot be used anymore since the column will be converted and it will cause slow performance on large database.

An example on comparing dates (from the SQL standpoint) is like this:

SELECT *
FROM   tableName
WHERE  CONVERT(DATETIME, dateSTRColumn, XXX) > GETDATE()

where XXX is the current format of the date stored as string.

or in other terms:-

SELECT convert(varchar(20), dateSTRColumn, 121) FROM tableName

this should give you some ideas.

Otherwise, you might have to write a simple custom function yourself.

Or change database.

Upvotes: 3

Ken White
Ken White

Reputation: 125707

You don't indicate what the underlying DBMS is, so you can probably do this in the SQL instead of a filter.

If you can't do that and the dataset isn't too large, you can convert the database date values to real dates and use them in the OnFilterRecord event:

procedure TForm3.Table1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
var
  TableDate: TDateTime;
begin
  TableDate := StrToDate(Table1DateField.AsString);
  Accept := (TableDate >= ADatePicker.Date) and
            (TableDate <= EndDatePicker.Date);
end;

Upvotes: 4

Related Questions