0

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?

Richard Holland
  • 2,663
  • 2
  • 21
  • 35

3 Answers3

4

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;
Ken White
  • 123,280
  • 14
  • 225
  • 444
  • It's NexusDB and proving quite hard to convert the string into a date. – Richard Holland Jun 21 '13 at 20:08
  • @Richard: What is the difficulty you're having? – Ken White Jun 21 '13 at 20:28
  • That looks like a date format of 'm/d/yyy' - I'd use the overloaded version of StrToDate and ensure that the dateformat is set correctly within before doing the comparison. @KenWhite is right though, you should use SQL from within NexusDB (TnxQuery?), rather than use a table - as long as you don't do a complex join it will still be editable. (from memory) – Mark Robinson Jul 18 '13 at 11:00
3

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.

Philo
  • 1,931
  • 12
  • 39
  • 77
0

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

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

  mytable.fittered:=true;
mr_gian55
  • 43
  • 8