Date Format Clarification

Classic Servoy
1

It was my understanding that when entering dates using the calander, auto enter or DateTime Stamp, the backend stores the complete date and time. When using the date property on a form to format a date, it just formats the display of value , and the actual value in the backend (SQL 2000) is the complete Date a Time format.

I have a method that searchs a field for a date range, if I format the values as MM-dd-yyyy hh:mm:ss in the properties and search it works fine, if I change the format to something else such as hh:mm:ss a and perform the search, I do not get results.

Here is a piece of my search method:

//Format date for Search
if (globals.Date1)
{
var d1 = globals.Date1
d1.setHours(00)
d1.setMinutes(00)
d1.setSeconds(00)
var df1 = utils.dateFormat(d1, ‘MM-dd-yyyy hh:mm:ss’)
}

if(globals.Date2)
{
var d2 = globals.Date2
d2.setHours(00)
d2.setMinutes(00)
d2.setSeconds(00)
var df2 = utils.dateFormat(d2, ‘MM-dd-yyyy hh:mm:ss’)
}

//validate date fields
if (globals.Date1 == null && globals.Date2 || globals.Date2 == null && globals.Date1)
{
plugins.dialogs.showInfoDialog(‘Warning’, ‘You must enter a start and end date’,‘OK’);
return
}

controller.find()

if (globals.Date1 && globals.Date2)
{
createdrecvd_datetime = df1+ ‘…’ + df2 + ‘|MM-dd-yyyy hh:mm:ss’;
}
else
{
createdrecvd_datetime = null;
}

controller.search()
application.closeFormDialog();
forms.CSServiceOrderSearchResults.controller.show();

When I debug I see that the date variables are formatted correctly

d1 Thu Dec 15 00:00:00 EST 2005

df1 12-15-2005 12:00:00

So why does changing the format in the properties window on a form, effect the search when I am creating variables in my method that should format the date regardless of the format in the properties window.

All I want to do is search the date field, excluding the time portion for a range, I read a post saying to use the # symbol, but could not get it to work on a manual search.

Any clarification would be appreciated

Thanks,
Erich

2

Hi Erich

Here’s how I do it. First I give the user a FID (Form in Dialogue) for them to work with. For UI clarity I prefer to keep away from forms appearing sometimes in browse mode and other times in Find. The klutzy among us tend to get confused.

The FID presents a popup where the user can choose whether they’re searching for an exact date, prior, after or a date range. In the first three cases they’re presented with a single global field to enter their choice. If a range then two global fields.

A FID also enables you to include other search options such as which date field to search in, and other search criteria

Once that’s established here’s the method that does the searching:

var dupFoundset = forms.comDetails.controller.duplicateFoundSet(); // for restoring existing found set if no results found

var date1 = new Date();
date1 = globals.gdate1;
date1.setHours(00);
date1.setMinutes(00);
date1.setSeconds(00);
var searchDate1 = utils.dateFormat(date1, 'dd-MM-yyyy');

if ( globals.gdate2 )
{
	var date2 = new Date();
	date2 = globals.gdate2;
	date2.setHours(00);
	date2.setMinutes(00);
	date2.setSeconds(00);
	var searchDate2 = utils.dateFormat(date2, 'dd-MM-yyyy');
}

currentcontroller.find();//Start the search

var operator = globals.goperator.substr(0,1); //Get the first character of the operator
var op = '';//Initialize the supplied operator

if (operator != '=')
{
	op = globals.goperator;//We don't need the = when searching
}

if ( operator != '>' && operator != '<' && operator != '.' )
{
	op += "#" //We only need to supply the # when it's equality or non equality
}

//Set the field values
if ( utils.stringPatternCount(op, '...') )
{
	if ( globals.gtext == i18n.getI18NMessage('7office.vl.recordcreated') )
	{
		creation_date = searchDate1 + op + searchDate2 + '|dd-MM-yyyy';
		created_by_id = globals.gutility2;
		com_sev_id = globals.gsevid;
	}
	if ( globals.gtext == i18n.getI18NMessage('7office.vl.recordmod') )
	{
		mod_date = searchDate1 + op + searchDate2 + '|dd-MM-yyyy';
		mod_id = globals.gutility2;
		com_sev_id = globals.gsevid;
	}
}
else
{
	if ( globals.gtext == i18n.getI18NMessage('7office.vl.recordcreated') )
	{
		creation_date = op + searchDate1 + '|dd-MM-yyyy';
		created_by_id = globals.gutility2;
		com_sev_id = globals.gsevid;
	}
	if ( globals.gtext == i18n.getI18NMessage('7office.vl.recordmod') )
	{
		mod_date = op + searchDate1 + '|dd-MM-yyyy';
		mod_id = globals.gutility2;
		com_sev_id = globals.gsevid;
	}
}

var count = currentcontroller.search();
globals.gshowselectcom = '';
// application.output('comUtility.searchDateUtility count = ' + count);

if (count == 0)
{
	var title = i18n.getI18NMessage('7office.dlg.alert');
	var msg = i18n.getI18NMessage('7office.dlg.norecords');
	var btn1 = i18n.getI18NMessage('7office.dlg.ok');

	plugins.dialogs.showDialog(title, msg,btn1);
	forms.comDetails.controller.loadRecords(dupFoundset);
	forms.comTable.controller.loadRecords(dupFoundset);
	forms.comDetails.recordStatus();
}
else
{
	var dupfs = forms.comUtility.controller.duplicateFoundSet();
	forms.comDetails.controller.loadRecords(dupfs);
	forms.comTable.controller.loadRecords(dupfs);
	forms.comDetails.recordStatus();
	forms.comMain.controller.show();
}

Hope this helps.