Re: Oracle 7.3 Date Query via Visual InterDev

Hello,

If FromDate and ToDate are DATE type, you need to do this: TO_DATE(to_char(:FromDate:,'MM/DD/YY')).

If FormDate and ToDate are STRING type, you need to make sure they are in 'MM/DD/YY' format (ie. "12/01/99").

Of course, after conversion, you will lose hours and minutes. You might want to do this:

>= to_date(to_char(:FromDate:,'MM/DD/YY')) and
< to_date(to_char(:ToDate:,'MM/DD/YY')) + 1

Gook Luck

Jong

In article <HrjU3.3913$n51.143549_at_news.inreach.com>,   "ASheth" <ashisheth_at_hotmail.com> wrote:
> Hi all,
> I am trying to write a web query in Visual InterDev which will
return a
> subset of records based upon user input (ie. A user will select the
date
> range for which he would like to see employee records). I am trying
create
> the SQL statement which will prompt for the start date and end date
for the
> range using the following statement:
>
> SELECT EMPAUX.SUPERNO, LABOUR_HISTORY.EMPLOYEENO,
> EMPLOYEES.SURNAME, EMPLOYEES.GIVEN_NAME,
> LABOUR_HISTORY.TIMEDATE,
> LABOUR_HISTORY.WORKORDERNO,
> SUM(LABOUR_HISTORY.HOURS_WORKED)
> AS SUMOFHOURS
> FROM EMPAUX, EMPLOYEES, LABOUR_HISTORY
> WHERE EMPAUX.EMPNO = EMPLOYEES.EMPLOYEENO AND
> EMPLOYEES.EMPLOYEENO = LABOUR_HISTORY.EMPLOYEENO
> GROUP BY EMPAUX.SUPERNO, LABOUR_HISTORY.EMPLOYEENO,
> EMPLOYEES.SURNAME, EMPLOYEES.GIVEN_NAME,
> LABOUR_HISTORY.TIMEDATE,
> LABOUR_HISTORY.WORKORDERNO
> HAVING (LABOUR_HISTORY.TIMEDATE >= TO_DATE(:FromDate:,
> 'MM/DD/YY')) AND
> (LABOUR_HISTORY.TIMEDATE <= TO_DATE(:ToDate:,
> 'MM/DD/YY'))
> ORDER BY EMPAUX.SUPERNO, LABOUR_HISTORY.EMPLOYEENO,
> LABOUR_HISTORY.TIMEDATE
>
> When I attempt to run this statement, I am prompted for the FromDate
and
> ToDate entries and I enter the correct format (10/25/99 and 10/31/99).
>
> The response that I get back is ORA-01840 - Input value not long
enough for
> date format.
>
> Is there anything that any of you can consider as being improper with
the
> way this statement is written?
>
> Thanks in advance for all of your support.
>
> Ashi Sheth
> Applied Aerospace Structures Corp.
> ashisheth_at_hotmail.com
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 04 1999 - 16:24:41 CST