clientdataset how to filter on a date value

[J. Hessing]

Hi,

I try to apply a filter on a clientdataset.
I only want records with a datevalue of today.

The next code doesn't work of course, since a String is not compatible with
TDateTime:

ClientDataSet_Reg.Filter := 'ClientDataSet_RegField_DateTime = ' +
DATE();

Trying to use the FormatDateTime() function on both the field and the DATE()
function does not work either, at runtime there will occur an error because
the FormatDateTime function could not be found...

Who knows how to do this correctly?

[George Christoforakis]

Try to decode the date and then pass it as one string. Also bare in mind
that it's sometimes safer to pass the date backwards YYYMMDD

George Christoforakis.

"J. Hessing" <jhes...@brightsoft.nl> wrote in message
news:3fbc...@newsgroups.borland.com...

[J. Hessing]

Thank you George ( or should I say Giorgos ;-) )

But this didn't help me, at runtime I received an error with the message
that the number is not a valid DateTime.
I finally browsed my Delphi manual and fortunately I saw an example like
follows:

DateEntered >= '1/1/1998'

So I tried the following:

ClientDataSet_Reg.Filter := 'ClientDataSet_RegField_DateTime >= ' + '''' +
FormatDateTime('dd/mm/yyyy',Date()) + '''' + ' and
ClientDataSet_RegField_DateTime < ' + '''' +
FormatDateTime('dd/mm/yyyy',Date()+1) + '''';

This works fine!
If you are wondering why I have used two expressions ( Date() and Date()+1 )
to compare with, that's because my records also contain a time value, so no
records would appear when comparing on an exact date.

John Hessing
"George Christoforakis" <nos...@nospam.info> wrote in message

[George Christoforakis]

Well, at least the formatdate worked... :-)
erm, what is going to happen if the regional sets of the date format is
different to the one you specify here with a '/'?

George Christoforakis.

"J. Hessing" <jhes...@brightsoft.nl> wrote in message
news:3fbc...@newsgroups.borland.com...

[J. Hessing]

It could be a problem indeed, so I tried it by setting the dateformat to US.
And yes, an error occurs if you try to set the filter.
Since this particular program is only going to be used by myself, it's no
problem.

But I think I could find a way to fix this problem.
One possibility I'm thinking of is changing the format according to the
current settings.
Another possibility is to temporarily change the internal dateformat
Delphi's using.

John

"George Christoforakis" <nos...@nospam.info> wrote in message

[George Christoforakis]

It's safer by using the system settings, isn't it?

One thing we tried and it worked in MSSQL (and if I remember well on MySQL
too and firebird... it's been a while since I used them last!) is the
YYYYMMDD format.
In this way the sql-server takes over to save the date.

good luck,
George Christoforakis

PS: yes it's Giorgos :-)

"J. Hessing" <jhes...@brightsoft.nl> wrote in message

news:3fbcb201$1...@newsgroups.borland.com...

[Wayne Niddery [TeamB]]

J. Hessing wrote:
> It could be a problem indeed, so I tried it by setting the dateformat
> to US. And yes, an error occurs if you try to set the filter.
> Since this particular program is only going to be used by myself,
> it's no problem.
>
> But I think I could find a way to fix this problem.
> One possibility I'm thinking of is changing the format according to
> the current settings.
> Another possibility is to temporarily change the internal dateformat
> Delphi's using.

Check the doc for your database to see what formats it accepts, you might
find there is one that is always acceptable regardless of regional settings
(e.g. perhaps DD-MMM-YYYY where MMM is the 3 char alpha month . If not, then
you need to be able to determine what the database setting is from the
client and format accordingly. If the database is on another machine then
you of course cannot go by the regional settings of the client machine.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson

[Wayne Niddery [TeamB]]

George Christoforakis wrote:
> Well, at least the formatdate worked... :-)
> erm, what is going to happen if the regional sets of the date format
> is different to the one you specify here with a '/'?

FormatDateTime is exactly the right thing to use to avoid regional setting
problems. What the database expects depends on the database server. In the
case of Interbase it does not look at regional settings, it accepts a
specific set of data formats and they are always valid. I expect most other
database servers will also always accept one or more specific formats
regardless of regional settings..

[Eduardo Bahiense]

> FormatDateTime is exactly the right thing to use to avoid regional setting
> problems. What the database expects depends on the database server. In the
> case of Interbase it does not look at regional settings, it accepts a
> specific set of data formats and they are always valid. I expect most
other
> database servers will also always accept one or more specific formats
> regardless of regional settings..

I usually use two global variables wich hold Server Acceptable date format
and
Server Date Separator as well.
I have experencied a problem when user set windows date format with
something like MM.DD.YYYY. In that cases FormatdateTime returns
11.18.2003 even passing 'MM/DD/YYYY'. So I have other global variable
wich holds DateSeparator and after FormatDateTime(GlobalDateFormat, Date)
I perform a string replace from Enviroment DateSeparator to my global
DateSeparator;

For example: MySQL

MyServerDateFormat := 'YYYY-MM-DD';
MyServerDateSeparator := '-';

MyDateString := StringReplace(FormatDateTime(MyServerDateFormat, Date),
DateSeparator,
MyServerDateSeparator, [rfReplaceAll,
rfIgnoreCase]);

For InterBase/FireBird
MyServerDateFormat := 'MM/DD/YYYY';
MyServerDateSeparator := '/';

Regards, Eduardo

> FormatDateTime is exactly the right thing to use to avoid regional setting
> problems. What the database expects depends on the database server. In the
> case of Interbase it does not look at regional settings, it accepts a
> specific set of data formats and they are always valid. I expect most
other
> database servers will also always accept one or more specific formats
> regardless of regional settings..

I usually use two global variables wich hold Server Acceptable date format
and
Server Date Separator as well.
I have experencied a problem when user set windows date format with
something like MM.DD.YYYY. In that cases FormatdateTime returns
11.18.2003 even passing 'MM/DD/YYYY'. So I have other global variable
wich holds DateSeparator and after FormatDateTime(GlobalDateFormat, Date)
I perform a string replace from Enviroment DateSeparator to my global
DateSeparator;

For example: MySQL

MyServerDateFormat := 'YYYY-MM-DD';
MyServerDateSeparator := '-';

MyDateString := StringReplace(FormatDateTime(MyServerDateFormat, Date),
DateSeparator,
MyServerDateSeparator, [rfReplaceAll,
rfIgnoreCase]);

For InterBase/FireBird
MyServerDateFormat := 'MM/DD/YYYY';
MyServerDateSeparator := '/';

Regards, Eduardo

[George Christoforakis]

Do you know if interbase accepts the YYYYMMDD format?
George Christoforakis.

[Eduardo Bahiense]

> Do you know if interbase accepts the YYYYMMDD format?

Actually I don't have Interbase installed.
FireBird 1.5 RC 7 accepts both
'YYYY/MM/DD' - 'YYYY.MM.DD' - 'YYYY-MM-DD' - 'YYYY MM DD' and
do not 'YYYYMMDD'
As you see it accepts any separator but requires a separator

It accepts 'MM' AND 'MMMM' too but requires 'YYYY' when formats are not
'M../D../Y..'

I don't know if it was inherited from Interbase

Regards, Eduardo

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.538 / Virus Database: 333 - Release Date: 10/11/2003