HOW TO: Work with and search date fields

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

HOW TO: Work with and search date fields

Postby mattman » Wed Feb 11, 2004 3:52 am

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.

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! :D
Matt Petrowsky
mattman
 
Posts: 160
Joined: Wed Aug 06, 2003 8:23 am
Location: Murrieta, CA

Postby pbakker » Wed Feb 11, 2004 10:34 am

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
 
Posts: 2822
Joined: Wed Oct 01, 2003 8:12 pm
Location: Amsterdam, the Netherlands

Postby Jan Blok » Wed Feb 11, 2004 12:31 pm

pbakker wrote: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
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby pbakker » Wed Feb 11, 2004 12:44 pm

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
pbakker
 
Posts: 2822
Joined: Wed Oct 01, 2003 8:12 pm
Location: Amsterdam, the Netherlands

Postby Jan Blok » Wed Feb 11, 2004 6:13 pm

var servertime = application.getServerTimeStamp();
Will be availeble in Servoy 2.0 rc5
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby Harjo » Mon Feb 23, 2004 4:14 pm

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?
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Postby Jan Blok » Tue Feb 24, 2004 4:26 pm

Exellent idea, will be availeble in Servoy 2.0 rc8
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby DFehrenbach » Thu Mar 18, 2004 6:34 pm

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?
DFehrenbach
 
Posts: 252
Joined: Sat Sep 13, 2003 6:48 pm
Location: Cleveland, OH

Postby Jan Blok » Thu Mar 18, 2004 6:58 pm

the result of utils.dateFormat(..) is a string...
use
var d1 = globals.fromdate
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby DFehrenbach » Thu Mar 18, 2004 7:15 pm

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?
DFehrenbach
 
Posts: 252
Joined: Sat Sep 13, 2003 6:48 pm
Location: Cleveland, OH

Postby Jan Blok » Thu Mar 18, 2004 7:43 pm

this should work:
Code: Select all
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()
Jan Blok
Servoy
Jan Blok
 
Posts: 2684
Joined: Mon Jun 23, 2003 11:15 am
Location: Amsterdam

Postby DFehrenbach » Thu Mar 18, 2004 7:52 pm

Works perfectly! Thanks very much for enduring my ignorance!
DFehrenbach
 
Posts: 252
Joined: Sat Sep 13, 2003 6:48 pm
Location: Cleveland, OH

Postby DFehrenbach » Thu Mar 18, 2004 9:06 pm

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?
DFehrenbach
 
Posts: 252
Joined: Sat Sep 13, 2003 6:48 pm
Location: Cleveland, OH

Postby IT2Be » Thu Mar 18, 2004 10:01 pm

That will be 'HH:mm:ss'
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Postby DFehrenbach » Thu Mar 18, 2004 10:05 pm

AH! many thanks!
DFehrenbach
 
Posts: 252
Joined: Sat Sep 13, 2003 6:48 pm
Location: Cleveland, OH

Next

Return to How To

Who is online

Users browsing this forum: No registered users and 2 guests