OpenNTF.org - ASND Better Date-Based View Ap

OpenNTF Code Bin

Edit Document Code By Date > Code Document

About This Code

Brief Description:

ASND Better Date-Based View Approach - Internationalized

Rating:

Not Rated Yet

Contributor:

Kevin Pettitt

Category:

Design Elements

Type:

Date/Time functions

Document Release:

1.0

Notes Version:

R7.x

Last Modified:

09 Jun 2007

OpenNTF Disclaimer

All of the program code and information presented in the OpenNTF.org Code Bin are provided "as-is", and should be used at your own risk. OpenNTF.org make no express or implied warranty about anything in the Code Bin, and OpenNTF.org will not be responsible or liable for any damage caused by the use or misuse of anything from this site. OpenNTF.org makes no guarantees about anything. Please thoroughly test all of the knowledge and code you find here before you attempt to use them in your production environment.

Code / Description

I took Sean Burgess's SNTT post "Date based views that don’t perform like dogs" (http://www.phigsaidwhat.com/Phigmentb/phigment.nsf/dx/sntt--date-based-views-that-dont-perform-like-dogs.htm?opendocument) and a subsequent suggestion in the comments about ensuring it would work for non-US date formats, and came up with this bit of sample code that show's a complete solution. Figured I'd better put it here so I'll be able to find it later on :-).

Usage / Example

Basically Sean's technique is to run a nightly agent (e.g. 12:10 AM) that revises all date-based view selection formulae so they become hard-coded for the current date. All I did was change the agent and the formula syntax so instead of a selection formula that looks like this:

ASNDtdy := [06/08/2007];
SELECT DeliveredDate = ASNDtdy

you would instead have this:

ASNDtdy := @Date(2007; 6; 8);
SELECT DeliveredDate = ASNDtdy

The latter being independent of the particular mmddyyyy or ddmmyyyy date format in use, either on the server or the client. NOTE: The syntax and spacing of the first line in the formula are VERY important as that is how the agent finds the relevant views. It doesn't matter if the formula you paste in initially has the current date or not.

The agent has been written to use the OpenLog error handling framework (from the OpenLog OpenNTF project available on this site). If you don't have that enabled you'll have to rem out those lines.

I think this should work in R6 but am pretty sure R5 is out.

Code Attachments

ASNDtdy.zip (45 Kbytes)

Comments

Posted by barry mcgovern on 06/09/2007 05:56:15 AMPerformance Difference

I've been using this method for quite some time and it works great. I'm just clarifying here though. Is there a difference in performance when you declare the date first instead of just putting it directly in the selection formula? Would the following syntax be slower then your syntax?
SELECT DeliveredDate = @Date(2007; 6; 8);

Posted by Kevin Pettitt on 06/09/2007 08:49:15 AMThe variable is there so the agent knows where to look

I doubt there is any performance difference worth mentioning. Certainly nothing that would override the value of have an automated agent that will make the change for all affected views. You *might* be able to measure a difference for the initial view rebuild that, come to think of it, should be triggered right after the change in the selection formula. Ongoing view usage should perform as well as any other view with a similar number of documents and with the same index refresh settings.
The thing about these views, regardless of the exact format of the selection formula, is that they have to have a full rebuild once a day. As long as the server and not the first unlucky user of the morning has to suffer through waiting for this rebuild, there should be no problem.