date filter <-> locale short date format

[abbath8]

Hi,

I have a dbXpress application, and there is a Clientdataset which has a date field. I would like to filter the dataset by the date field. So it works for ex:

datefield >= '2005.05.24' and datefield <= '2005.05.25.'

IF the short date format (in the regional settings) is 'yyyy.MM.dd.'
BUT if the short date format is 'yyyy. MM. dd.' the above filter (even with: datefield >= '2005. 05. 24.') doesn't work.

I should override the locale settings but I don't know how...(it is not a solution to set the correct short date format on every machine where the application runs, because of the system privileges)

Plz help, how to make the date filter work on machines where the locale is different?

abbath8

 

[towerbase]

The ShortDateFormat is a variable that you can change locally in your application.

It would be a good idea to restore the ShortDateFormat variable to its original value after you have done your filtering.

Your code might look like:

var
 oldSDF: string;
...
begin
...
 oldSDF := ShortDateFormat;
 try
  ShortDateFormat := 'yyyy.MM.dd';
  ...
  Filtering process
  ...
 finally
  ShortDateFormat := oldSDF;
end;

Note that the ShortDateFormat variable applies to your application only and not the rest of the applications running on the computer.

Andrew
Hampshire, UK

 

[abbath8]

towerbase thx for the tip, i tried it but the parse error still remains...

 

[abbath8]

I noticed that the ShortDateFormat setting affects how to substitute dates as strings but not the format which is used in parsing...do I see it right?

What would be the solution then?

 

[donvittorio]

You could use FormatDateTime to format the date that you are putting in your filter
e.g.

  sFilter := QuotedStr(FormatDateTime('yyyy.mm.dd', Date));
  DataSet.Filter := 'datefield >= sFilter and datefield <= sFilter'

Steve

 

[towerbase]

Another option might be to use the Year, Month and Day functions of the Filter property.

For example, the following code seems to work on my computer (Delphi 7 and Windows XP Pro) with a variety of date formats.

 CDS.Filter := 'Year(datum)=2005 and Month(datum)=4 and Day(datum)=27';
 CDS.Filtered := TRUE;

Obviously, you would replace the hard coded year, month and day values with variables.

Andrew
Hampshire, UK

 

[abbath8]

FormatDateTime doesn't help. It does the same as above. Just formats the string but the parse error still remains.
Something is needed to tell the program how to parse a date field in a filter expression...

 

[abbath8]

The Year, Month, Day works well for an exact date, but if I want to give a range for my date field "datum"...

For example: 2004.05.16 - 2005.01.11.

How to do this with Year, Month, Day?

 

[towerbase]

Probably the easiest way to do this is to use the OnFilterRecord event handler instead of the Filter property.

Here is a simple example:

procedure TForm1.cdsFilterRecord(DataSet: TDataSet; var Accept: Boolean);
var
  FormatSettings: TFormatSettings;
  FirstDate: TDate;
  LastDate: TDate;
begin
  FormatSettings.ShortDateFormat := 'dd/mm/yyyy';
  FormatSettings.DateSeparator := '/';
  FirstDate := StrToDate ( '16/05/2004', FormatSettings );
  LastDate := StrToDate ( '11/01/2005', FormatSettings );
  accept := ( DataSet.FieldByName('datum').AsDateTime >= FirstDate ) and
            ( DataSet.FieldByName('datum').AsDateTime < LastDate );
end;

In your real code, you would set up the FirstDate and LastDate variables somewhere else so they don't get evaluated for each record.

Andrew
Hampshire, UK

 

[abbath8]

I found the problem! Giving ShortDateFormat is not enough. The DateSeparator must be also given (even if it is the same '.' as the default). Before the filter:

DateSeparator := '.';
ShortDateFormat := 'yyyy.mm.dd.';

and then the filter works correctly. I dont't understand but works...

So thanks for the help!