another date range problem

Classic Servoy
1

Having trouble searching for date ranges where dates are entered manually, i.e. 12-27-2006, and programmatically i.e. new Date(). Code is:

var dateStart = plugins.dialogs.showInputDialog(‘Date Range’,‘START Date, i.e. 6-1-2006’);
globals.gDateT1 = dateStart;
var dateEnd = plugins.dialogs.showInputDialog(‘Date Range’,‘END Date, i.e. 6-30-2006’);
globals.gDateT2 = dateEnd;

globals.srDateRange();
var searchDate = globals.gDateT1 + ‘…’ + globals.gDateT2 + ‘|MM-dd-yyyy’
controller.find();
date1 = searchDate;
controller.search();
controller.sort(‘date1 asc’);
forms.List3.controller.show();

Application output looks like this: 11-20-2006…11-27-2006|MM-dd-yyyy
but doesn’t find the machine-entered date. Also tried #11-20-2006…11-27-2006|MM-dd-yyyy with same result.
Help! TIA

2

Steve,

Are your globals dates or strings?

They should be string formatted as the format added to your search string…

Ciao

3

Solved it with the following code. Probably not “Best Practice” as I’m a newbie, but seems pretty robust. Comments appreciated.

Global Method:

// Get dialog TEXT and convert to start DATE with 00:00:00
var start = plugins.dialogs.showInputDialog(‘Date Range’,‘Enter START Date, i.e. 06-01-2006’);
if (start == null)// If empty, exit method
{
globals.gStartDate = null;
return;
}
if (start.length != 10)// Validate format to facillitate parsing
{
application.beep()//show dialog
var thePressedButton = plugins.dialogs.showErrorDialog(‘Formatting Error’, ‘Please use leading zeros and 4-digit years.’,‘OK’);
globals.gStartDate = null;
return;
}
var mymonth = utils.stringLeft(start, 2);
var myday = utils.stringMiddle(start, 4, 2);
var myyear = utils.stringRight(start, 4);
var startdate = new Date(myyear, mymonth-1, myday);//Convert text to date

//Set time to start of range
startdate.setHours(00);
startdate.setMinutes(00);
startdate.setSeconds(00);

globals.gStartDate = startdate;//This is the global used in form-specific methods

// Get dialog TEXT and convert to end DATE with 23:59:59
var end = plugins.dialogs.showInputDialog(‘Date Range’,‘Enter END Date, i.e. 07-05-2006’);
if (end == null)// If empty, exit method
{
globals.gEndDate = null;
return;
}
if (end.length != 10)// Validate format to facillitate parsing
{
application.beep()//show dialog
var thePressedButton = plugins.dialogs.showErrorDialog(‘Formatting Error’, ‘Please use leading zeros and 4-digit years.’,‘OK’);
globals.gEndDate = null;
return;
}
var mymonth = utils.stringLeft(end, 2);
var myday = utils.stringMiddle(end, 4, 2);
var myyear = utils.stringRight(end, 4);
//Convert text to date
var enddate = new Date(myyear, mymonth-1, myday);

//Set time to end of range
enddate.setHours(23);
enddate.setMinutes(59);
enddate.setSeconds(59);

globals.gEndDate = enddate;//This is the global used in form-specific methods

Form-Specific method:

//Call global date-range dialog
globals.sr_DateRange();

if (globals.gStartDate == null || globals.gEndDate == null)// Exit method
{
return;
}

//Format to strip out day-of-week, etc.
var DateStart = utils.dateFormat(globals.gStartDate,‘MM-dd-yyyy HH:mm:ss’);
var DateEnd = utils.dateFormat(globals.gEndDate,‘MM-dd-yyyy HH:mm:ss’);

//Set up/execute search
controller.find();
date1 = DateStart + ‘…’ + DateEnd + ’ |MM-dd-yyyy HH:mm:ss’;
integer1 = ‘>= 1’;
controller.search();

//Sort
controller.sort(‘date1 asc’);

//Go to form (layout)
forms.List3.controller.show();

4

You have missed a very helpful function: utils.DateFormat. That allows you to format a date to a string and vice versa. If you look at my code, you see it goes a bit faster (shorter):

Your global method

var vStartString = plugins.dialogs.showInputDialog('Date Range','Enter START Date, i.e. 06-01-2006');
if (!vStartString) {
	return null;
}
var vStartDate = utils.dateFormat(vStartString, 'dd-MM-yyyy');
vStartDate.setHours(0);
vStartDate.setMinutes(0);
vStartDate.setSeconds(0);

var vEndString = plugins.dialogs.showInputDialog('Date Range','Enter END Date, i.e. 06-01-2006');
if (!vEndString) {
	return null;
}
var vEndDate = utils.dateFormat(vEndString, 'dd-MM-yyyy');
vEndDate.setHours(23);
vEndDate.setMinutes(59);
vEndDate.setSeconds(59);

var vResult = utils.dateFormat(vStartDate, 'MM-dd-yyyy HH:mm:ss') + '...' + utils.dateFormat(vEndDate, 'MM-dd-yyyy HH:mm:ss') + ' |MM-dd-yyyy HH:mm:ss'; 

return vResult;

Your form method:

//Call global date-range dialog
var vSearchString = globals.sr_DateRange();
if (!vSearchString) {
	return null;
}

//Set up/execute search
controller.find();
date1 = vSearchString;
integer1 = '>= 1';
controller.search();

//Sort
controller.sort('date1 asc');

//Go to form (layout)
forms.List3.controller.show();

You can also see that you don’t need the global variables. You can just let your global method return your desired result and “grab” the resulting string directly in your form method.

Hope this helps!

5

I changed the date format in the code section from dd-MM to MM-dd and it works great. Thanks for the tip!

6

I stumbled about the format, too. But you should provide a better example than 06-01-2006, because that could be anything. You better ask for something like 06-22-2006, then everybody knows what format you expect :wink:

7

Hi,
There’s one little performance improvement you can achieve here:

Put your sort step above the search step:

//Set up/execute search
controller.find();
date1 = vSearchString;
integer1 = '>= 1';
[b]//Sort
controller.sort('date1 asc'); 
controller.search();[/b]

If you put the sort below the search step, the query is fired twice.
first time to fetch results, second time to fetch + sort.
Putting the sort above the search , allows servoy to build the complete query including ORDER BY before firing.