Because of JavaScript and SQL, the method for searching on date fields is actually a bit more specific. You might think you're searching a date field, when in fact, you're searching a DATETIME field.
A datetime field is a unique field that includes both the date and the time combined into one field.
Because of SQL systems being much more specific, if you have a servoy field formatted to show only the date portion there may be a hidden portion which includes the time part of a datetime field.
If you're only going to be searching based on the date part of a datetime field then you need to make sure and ADJUST your input OR your search.
INPUT ADJUSTMENT
Using the following code to adjust your time portion of a new date.
- Code: Select all
var today = new Date();
today.setHours(00);
today.setMinutes(00);
today.setSeconds(00);
SEARCH ADJUSTMENT
TIP: Using the # symbol will negate the time portion of a datetime field.
If a datetime field contains 5/16/2005 14:53:06 and you search for 5/16/2005 you won't get the results you expect. This is because you need to find any time with the 24 hours found in 5/16/2005.
Use the tip above and search with the # symbol.
However, before you do that, you need to know that JavaScript dates, when used within methods, can be formatted in many different ways. This means you need to tell the field what type of structure your date is in. To do this you need to supply the format.
Using the pipe character along with the supplied formatting is what you need to provide when creating a search in a method.
Additionally, if you are using an operator such as greater than, less than, greater than or equal to or less than or equal to, you need to omit the # in front of the date. The following code uses two global fields. One global field is an operator from the following list (=, !, <, >, <=, >=)
Global Field 1 = operator (text) - value list above used
Global Field 2 = inputDate (date)
- Code: Select all
currentcontroller.find();//Start the search
var searchDate = utils.dateFormat(globals.dateInput, 'MM/dd/yyyy');
var operator = globals.operator.substr(0,1); //Get the first character of the operator
var op = '';//Initialize the supplied operator
if (operator != '='){
op = globals.operator;//We don't need the = when searching
}
if ( operator != '>' && operator != '<' ){
op += "#" //We only need to supply the # when it's equality or non equality
}
//Set the field values
forms.myform.dateField = op + searchDate + '|MM/dd/yyyy';
currentcontroller.search();
Notice we take a date from a supplied global field and we format the date into the structure of MM/dd/yyyy. We then supply this same information to the field being searched. The # symbol is used for not equals and equals searches. If the search includes a '>' or '<' then we don't include the #.
Remember to post your code here if you figure out something cool!
)
