Re: Error in FORMS4.5 using Sysdate

A Weiden wrote:
>
> I placed the following SQL Statement in a forms 4.5 module :
>
> SELECT nr FROM My_table WHERE My_date > SYSDATE;
>
> My_date is of Type DATE.
>
> The table My_table has 4 rows, with one row matching the where clause.
> In FORMS the NO_DATA_FOUND exception is raised, the matching row is not
> returned by the statement. Using exactly the same statement in SQL plus
> (logically) returns the matching row.
>
> I've tried several other queries with Date-functionality and always get
> wrong results.
> Can anyone solve this problem ???
> Thanks
I believe that unless you specify otherwise, when doing straight date comparrisons, the time portion is truncated to 00:00 (midnight). So in the example above if the the only difference between the dates was one was earlier that day then nothing would be returned. For your select statment to work, the DATE portion would have to be later than the current DATE. Now if you want to compare DATETIME stamps then you would need to do something like TO_CHAR( My_date, 'JHH24MISS' ) > TO_CHAR( SYSDATE, 'JHH24MISS' ).

--
Ken Niedermeyer
Boeing Central Information Systems
Ken.Niedermeyer_at_boeing.com