foundsetFilter and Date

Classic Servoy
1

Any suggestions as to what I’m doing wrong as I want to filter the foundset based on a date, the result is always empty (no data):

valueDateArg = '#' + utils.dateFormat(valueDateArg,'yyyy/MM/dd') + '|yyyy/MM/dd'
var success = forms.frm_lst_route_messages.foundset.addFoundSetFilterParam('value_date','=',valueDateArg, 'routeFilterDate')
forms.frm_lst_route_messages.foundset.loadAllRecords()
2

Hi,

if value_date is a datetime field then you are querying now for only the 00:00:00 time of that date…

Welcome to the (sql) datetime world…

Most of the sql server vendors have their own date and datetime functions dialect.

My advice : stay as much as possible to Servoy datetime searching and if not possible to ANSI sql.

Regards,

3

ffow,

The hash (#) for date searches is only support in find/search, not in table filter.
(Noet thar the hash as case-insentive search is supported in filters).

You can use the between operators to find all messages of the day.
The object arguments needs to be an array of 2 date objects:

forms.frm_lst_route_messages.foundset.addFoundSetFilterParam('value_date','between',[date1, date2], 'routeFilterDate')

Rob

4

rgansevles:

...between',[date1, date2]...

How can I do that, if date2 should be infinity future? I want to filter for records ‘>=’ the current year.

5

Hello deezzub,

To get the records ‘>=’ current year:

var curDate = new Date();		                                      // Get the new date object
curDate.setMonth(0,1);			                                      // Set the date to 1st Jan of the year
foundset.addFoundSetFilterParam('date_column_name', '>=', curDate);
foundset.loadAllRecords();

Hope this helps.

Lopamudra
lopamudram@mindfiresolutions.com

6

Good idea, but be sure to set it really to 1st January at 00:00:00, setMonth() sets only the month, not the time.
So you would miss the (small) time range up to the current time on 1st January.

You can try this here yourself with the -button:
http://www.w3schools.com/jsref/jsref_setmonth.asp

var d = new Date();
d.setMonth(0,1);
d.setHours(0,0,0,0);