Hi,

Lazarus 0.9.30

I want to filter a TDbf. The field is a date.
When I try in Filter in Lazarus :
DATUM >= '1/1/2011'
or
DATUM >= "1/1/2011"
or
DATUM >= '1.1.2011'

In my application :
tblTik.Filter:='DATUM>='''+DateToStr(XferStart)+'''';

I get an error :
Index based on unknown field ">=".

What am I doing wrong ?

Regards,

Koenraad Lelong.

Koenraad Lelong wrote

tblTik.Filter:='DATUM>='''+DateToStr(XferStart)+''''; I get an error : Index based on unknown field ">=".

My guess is take a look at the location of your apostrophizes ('). You have 3 of them in a row in a couple of cases, which will interpreted as "print chr(39)" and not as a text dilimeter.

Hi,

Actually, that line comes from a Delphi application, where this works fine. Could you tell me what the difference is between chr(39) and 'text delimiter' ? AFAIK in Pascal, the text delimiter _is_ chr(39). And if there were errors, would the application compile ? Because if the number of apostrophes would be wrong, the string would not be closed.

I know with databases sometimes you have to use ', sometimes you have to use ". But I tried both :

tblTik.Filter:='DATUM>="'+DateToStr(XferStart)+'"';

Gives the same error.

Anyway, thanks for your response.

Koenraad Lelong.

This code works in delphi?
Comparing strings doesn?t work for dates, I guess. Using mm/dd/yyyy, "05/01/2012" is less/before than "05/02/2011"

2012/1/30 Koenraad Lelong :

to be able to compare dates as strings, you should always set the format as year-month-day

Exactly!

2012/1/31 Michael Joyner :

to be able to compare dates as strings, you should always set the format as year-month-day

Use QuotedStr and FormatDate for that matter.
sql := 'where datum >= ' + quotedstr ( formatdate ( 'yyyy-mm-dd', now ) );

2012/1/31 Guionardo Furlan

Michael Joyner wrote

to be able to compare dates as strings, you should always set the format as year-month-day

+100

i tend to use yyyymmddhhkkssjjjjj where kk is minutes and jj us hundreths of seconds where available... at least this for _simple_ before/equal/after comparisons... anything else requires more work ;)

Guionardo Furlan wrote

This code works in delphi? Comparing strings doesn?t work for dates, I guess. Using mm/dd/yyyy, "05/01/2012" is less/before than "05/02/2011"

...
Hi,

I don't want to compare "dates as strings", I want to compare dates with other dates, as dates. The thing is the filter in TDbf is a string. So how would I specify that filter if not as a string ?

In Lazarus I tried also
datum >= '2011-01-01'
datum >= "2011-01-01"
Same error : Index based on unknown field ">="
That's when I enter the filter with the TDbf active. If I enter this with the TDbf closed, and then open the TDbf, the filter just disappears.

AFAIK, TDbf is has no SQL, so this can't be used. Besides, with Firebird ... where datum >= '5.9.2011'
is perfectly valid to have all dates from/including sept 5 2011, I just checked.

So, any other suggestions ?
Thanks for your replies.

Koenraad.

Koenraad Lelong wrote

AFAIK, TDbf is has no SQL, so this can't be used. Besides, with Firebird ... where datum >= '5.9.2011' is perfectly valid to have all dates from/including sept 5 2011, I just checked.

PMFJI, but don't, please DON'T use this date notation. dd/mm/yy or mm/dd/yy notations are often parsed depending on OS locale settings and in any case ambiguous between e.g. US and Europeans.

As earlier suggested, try to be unambiguous in Firebird as well and use 'CCYY-MM-DD HH:NN:SS.nnnn' or 'YY-MM-DD HH:NN:SS.nnnn' (".nnnn" element is optional).

So, any other suggestions ? Thanks for your replies.

Sorry, no idea about the dbf filter...

Regards,
Reinier

Hi,

For me one thing is clear now and hopefully really unabiguous : I'm going to use 'CCYY-MM-DD HH:NN:SS.nnnn' or 'YY-MM-DD HH:NN:SS.nnnn' (and without time-info if this is not needed).

I have long searched how to represent dates in an sql-select. Finally I just copied the date from a select *, where the date is returned as 5.9.2011 for sept 5, 2011.
I don't think I will forget this now ;-)

Unfortunately, this does not help with the tdbf.

Thanks,

Koenraad.

Hi,

IIRC, first of all you need an index based on the field(s) you want to filter. The error is shown because the index "DATUM" doesn't exists. Here is the real problem, I don't know how to create an index of ftDate. If I try an error is shown because only string and float fields are allowed. But you could convert your database and store all dates as strings. To format the date right you can use

procedure TForm1.FormCreate(Sender: TObject);
begin
   DefaultFormatSettings.DateSeparator:='.';
   DefaultFormatSettings.ShortDateFormat:='yyyy.mm.dd';
...
end;

With this lines DateToStr() should convert it right.

The filter property wants the name of the filter to compare with, not the name of the field.

  dbf1.Filter:='INDEXNAME>='+QuotedStr(DateToStr(XferStart));
  dbf1.Filtered:=true;

If the date-field is of ftString, the dates are correctly formatted and the index is build it should work.

Hope this helps.

regards

Ingo

Hi Ingo,

Unfortunately, this does not help. The application I'm writing extracts data from the database. The input into the database is handled by an old application (dbase or a derivate), and I don't have the sources of it. So I can't modify the database.

If there is no solution, I will have to stick with Delphi then. Is this a deliberate omission, or a bug ? Should I file a bugreport ?

Thanks,

Koenraad.

There's no difference between Delphi's tDbf and Free Pascal's tDbf. Although since tDbf is not maintained anymore, the Free Pascal version has some fixes which aren't in the 'official' tDbf version.

And if I remember exactly, tDbf can not filter on '>' and '<' but it uses 'gt' and 'lt'. Why it did work on Delphi I don't know, though.

Joost.