Button search between two dates

[kizziebutler]

Hello I would like to create a search button (push button) that will allow the user to input two dates and search fields between two dates. I have be able to do this successfully when search for numeric fields but not sure how to code the pl/sql (when-button push) for the dates, here a sample of my code for searching for numeric data such as deptno. Your help would be much appreciated.

DECLARE
v_where VARCHAR2(2000) := NULL;
v_ename VARCHAR2(30) := :control.ename;
v_deptno NUMBER := :control.deptno;
BEGIN
--
IF v_deptno IS NOT NULL
THEN
v_where := 'DEPTNO = ' || v_deptno;
END IF;
set_block_property('EMP', DEFAULT_WHERE, v_where);

-- Query
go_block('EMP');
execute_query;
EXCEPTION
WHEN OTHERS
THEN
message(SQLERRM);
END;

 

[kizziebutler]

I know the sql should do some like this but I cannot get the search to recognise between the two text fields on the form. ie DATE1 DATE2 SEARCH

SELECT .... WHERE DATE BETWEEN DATE1 AND DATE2;

 

[lewisp]

Assuming you are using Oracle Forms, are the fields on your form DATE or CHAR datatypes?

 

[kizziebutler]

I have tried changing them from date to varchar. I can search using it as char. Oracle forms Yes. The date filed is date in oracle and the search field set as date or varchar.

 

[kizziebutler]

I have used to_char(flightdate, 'DD-MON-YYYY') I can now search on one single field as the field declared as a varchar2(11), but cannot search between two dates to retrieve all flights between the departure date and return date. Can you provide any suggestions.

 

[kizziebutler]

I would really appreciate some help, if possible.

 

[lewisp]

Try using TRUNC on your database date if possible.

eg. [tt]WHERE Trunc(date_column) BETWEEN dateval1 AND dateval2;[/tt]

Or if this impacts on performance, add 1 day to dateval2:

[tt]WHERE date_column BETWEEN dateval1 AND dateval2 + 1;[/tt]

 

[kizziebutler]

That okay, but it is how I write the plsql to accept the parameters from the field data into the button search.