HOW TO: Work with and search date fields

SEARCHING ON DATE FIELDS

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.

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)

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! :D

Here's my contribution: If you have records with start and enddates and you want to switch between looking at all records or looking at the active records (active being: No enddate OR an enddate in the future), this would be the code for getting the active records:

controller.find();
enddate = '^' //Gets records where the enddate is null
controller.newRecord();
enddate = ">=" + utils.dateFormat(new Date(),'dd-MM-yyyy')+ '|dd-MM-yyyy'; //Where enddate >= than current date
controller.search();

For viewing all records you can use loadAllRecords.

One question on my part: newDate(), does it take the systemdate on the server where Servoy server is running, or the system date of the machine where the client is running? (sorry, can't test it myslef since both server and client are the same machine :? )

Paul

pbakker:
One question on my part: newDate(), does it take the systemdate on the server where Servoy server is running, or the system date of the machine where the client is running?

Local machine date (in timezone independent way), only audit trail(tracking) is done with server time

Would it be possible to get a function to get the current date from the server to work with? Incorrect timesettings on the client would cause incorrect data....

Paul

var servertime = application.getServerTimeStamp();
Will be availeble in Servoy 2.0 rc5

That is a nice feature, but can you also implement this under:
colum info --> Auto Enter?

Now you have only the option: creation datetime and modification datetime.
I pressume that those functions use the local (client time)
So I would like to see: creation serverdatetime and modification serverdatetime.

Is this possible?

Exellent idea, will be availeble in Servoy 2.0 rc8

The code below

//findPreviouslyEntered[TEEntry]
// This method will retrieve all of the entry records
globals.setFromToDates(1); // generates globals.fromdate and globals.todate to be Yesterday and today respectively
var d1 = utils.dateFormat(globals.fromdate, 'MM-dd-yyyy');
var d2 = utils.dateFormat(globals.todate, 'MM-dd-yyyy');
controller.find();
//userentering = security.getUserName()
dateentered = d1+'...' + d2+'|MM-dd-yyyy' ;
controller.search();

will only find dates with dateentered with the from date (yesterday). It will not find any records that are equal to the todate (today)

After checking the actual values for dateentered in the table they all have a hh:mm:ss value of '12:00:00'

after trying the following:

//findPreviouslyEntered[TEEntry]
// This method will retrieve all of the entry records
globals.setFromToDates(1); // generates globals.fromdate and globals.todate to be Yesterday and today respectively
var d1 = utils.dateFormat(globals.fromdate, 'MM-dd-yyyy hh:mm:ss');
d1.setHours(00);
d1.setMinutes(00);
d1.setSeconds(00);
var d2 = utils.dateFormat(globals.todate, 'MM-dd-yyyy hh:mm:ss');
d2.setHours(23);
d2.setMinutes(59);
d2.setSeconds(59);
controller.find();
//userentering = security.getUserName();
dateentered = d1+'...' + d2+'|MM-dd-yyyy hh:mm:ss' ;
controller.search();

I get an error stating that "setHours is not a function"

Any suggestions?

the result of utils.dateFormat(..) is a string...
use
var d1 = globals.fromdate

Thanks for your fast response, but I still have problems.
I've changed the code to be

//findPreviouslyEntered[TEEntry]
// This method will retrieve all of the entry records
globals.setFromToDates(1); // generates globals.fromdate and globals.todate to be Yesterday and today respectively
var d1 = globals.fromdate;
var d2 = globals.todate;
controller.find();
//userentering = security.getUserName();
dateentered = d1+'...' + d2;
controller.search();

and it yields the following value for the search:
dateentered = Wed Mar 17 12:08:16 EST 2004...Thu Mar 18 12:08:16 EST 2004
the records returned are ALL of the records in the table.

All I want are the records from 3/17/2004 00:00:00 to 3/18/2004 23:59:59
how do I load the find criteria for this date range?

this should work:

globals.setFromToDates(1)

var d1 = globals.fromdate
d1.setHours(00) 
d1.setMinutes(00) 
d1.setSeconds(00)
var df1 = utils.dateFormat(d1, 'MM-dd-yyyy hh:mm:ss')  

var d2 = globals.todate
d2.setHours(23) 
d2.setMinutes(59) 
d2.setSeconds(59) 
var df2 = utils.dateFormat(d2, 'MM-dd-yyyy hh:mm:ss') 

controller.find() 
dateentered = df1+'...' + df2+'|MM-dd-yyyy hh:mm:ss'  
controller.search()

Works perfectly! Thanks very much for enduring my ignorance!

Well, Maybe not so perfectly! :(
the search criteria:

dateentered = df1+'...' + df2+'|MM-dd-yyyy hh:mm:ss'

yields the following value:

03-17-2004 12:00:00...03-18-2004 11:59:59|MM-dd-yyyy hh:mm:ss

when viewed in the de-buger
the search does not show any entries entered after noon today.
I'm using the OO:OO:OO 23:59:59 values to set the time portion of the fields, but how do I specify that I mean a 24 hour clock instead of AM and PM?

That will be 'HH:mm:ss'

AH! many thanks!

How can I do a range search on dates ignoring the time section of it.

I tried something like the following:

var frmObj = forms.form1;
frmObj.controller.find();
frmObj.entry_date = "#" + utils.dateFormat(globals.srchDate1, "dd-MM-yyyy") +
"..." + utils.dateFormat(globals.srchDate2, "dd-MM-yyyy") + "|dd-MM-yyyy";
frmObj.controller.search();

This some how returns all the records.

I am doing anything wrong here??

Thanks