Teufel
|
| Posted: 02/18/2002, 12:07 PM |
|
Hi,
I have a page composed of a search form to filter information and a grid form just below it. This search form has two text input fields for a 'start date' and for an 'end date' respectively (as described in the article http://www.gotocode.com/art.asp?art_id=21&).
What I want is to show only grid entries that have 'date_field' between 'start_date' and 'end_date'.
My problems are:
1) I have to validate the date entries for wrong formatting BEFORE the grid form is shown, otherwise I get a database error. For example, supposing I am using dates as 'dd/mm/yyyy' and enter as 'end_date' the value '18' instead of '18/02/2002', I get the following database error (only relevant parts are shown):
Warning: PostgreSQL query failed: ERROR: Bad timestamp external representation '18' in /home/httpd/html/db_pgsql.inc on line 67
Database error: Invalid SQL: select s.request_date as s_request_date, ... from request_table s, .... where ...... AND (s.request_date<='18' and ....)
PostgreSQL Error: 1 (ERROR: Bad timestamp external representation '18' )
Session halted.
I imagine I could format the date in the OPEN event of the GRID FORM, but what if the date is wrong as above? Where should I output some error message to the user and how to avoid the grid form to have the *wrong filter* applied to it? (or should I hide the grid form in such a case, more or less as described in tip http://www.gotocode.com/art.asp?art_id=54& "How to show/hide forms(for templates)" -> this would though not work for me since I am not using templates)
2) Second problem is: when I add two search fields, one for the 'initial date' and another for the 'end date', the comparison generated to filter things goes somewhat like below:
WHERE grid_form_date >= initial_date and grid_form_date <= end_date
So if I want to show all records from 15/feb/2002 to 18/feb/2002 (inclusive), I rather endup showing only records from 15/feb/2002 up to 17/feb/2002 and not 18/feb/2002 (that is, '<=end_date' brings me only records less than or equal to the zero hour of end_date, in fact EXCLUDING the last day). It is in fact the same as saying the following:
WHERE grid_form_date >= initial_date and grid_form_date < end_date
The solution would be to *add 1 day* to the end date before actually submitting the query to the database. Where should I perform this action? (some search form event? some grid form event?). I suppose this same problem should occur with other databases and not only with PostgreSQL.
TIA,
Teufel
|
|
|
 |
Nicole
|
| Posted: 02/19/2002, 5:22 AM |
|
Teufel,
1. You may force user to enter date in proper format or check the entered value to be valid date value. Search for JavaScript code that could be used for this purpose (on http://www.faqts.com or http://www.hotscripts.com or http://javascript.internet.com/ or elsewhere). You may integrate it to CC page.
Then date should be converted to db date format before sql query executed. Refer to article on this site that may help you on this point:
http://www.gotocode.com/art.asp?art_id=132&
2. try to add 1 day to end_date field. I'm nit familiar with Postgresql functions but I suppose there's one that could add giving time period to date field. The syntax in CC would be
Open event of Grid form:
PHP
$sWhere = str_replace(get_param("enad_date"), dateadd_func(get_param(end_date)), $sWhere);
|
|
|
|
cornwell
|
| Posted: 02/19/2002, 1:05 PM |
|
try this one
http://javascript.internet.com/forms/format-date.html
|
|
|
|
|
