Relational operators

Classic Servoy
1

Hi,

I checked this new option and I think it’s great: I built a “start date - end date” filtering system in 3 minutes! :)

Only a little thing: I set up a relation from customer to orders based on the following fields:
CUSTOMERS ORDERS
customerid = customerid
startdate >= orderdate
enddate <= orderdate

Startdate and EndDate are globals.
All works fine except the “<=” operator, that behaves exactly as the “<” operator. For instance, if a customers has an order dated 29-8-2003, if I set up a period from 1-8-2003 to 30-8-2003, the order is displayed in the portal. Everything ok if I also choose from 29-8 to 15-9.
But, if I write from 1-8 to 29-8, the order disappears.

2

Is there a time component in the date columns? if so that can influence the behaviour (time is like set to 0:00 in that case)

3

Jan Blok:
Is there a time component in the date columns? if so that can influence the behaviour (time is like set to 0:00 in that case)

No: the field is formatted as dd/MM/yyyy.
But I noticed a strange thing: if I re-enter tha date manually, the record is correctly displayed in the portal.
But I can’t see any difference between a record working and one not working.

I’ll try to make some other test…

4

A format is only a display filter, datetime type in firebird is realy date+time, so when you fill the dates via field calender type you also insert a time.
this means if you provide a date to the relation which has as time ‘0:00’ you get the behaviour as described.
If the field is datetime you only can get the wanted behaviour if you set the time element from the date for lower boundary to 0:00.0.000 and the high boundery to 23:59.59.999
(this is waht happens with servoy date search if you use operatos like ‘<=’, ‘#’,etc. in date fields)
If your datebase supports plain ‘date’ type you migth consider switch the type (if possible), in future release of Servoy we have planned support for ‘date’ and ‘time’ only fields if suported by db.