Re: Date

In article <3818C886.E71FF6DF_at_vnl.nl>, Frank van Bortel <f.van.bortel_at_vnl.nl> writes
>Andrew Longworth wrote:
>
>> If I have a table with 3 fields, emp_no, ref_no and request_date. I at one
>> point enter data into emp_no and ref_no so the table looks like this :-
>>
>> emp_no ref_no request_date
>> 20 3
>> 38 5
>>
>> At at later date I want to search the table and find wich entries has no
>> date attached to them.
>> I have tried queries such as
>> select * from table_1
>> where request_date='';
>
>If you try to find rows WITHOUT a date, change the where to
>where request_date IS NULL.
>
>>
>> but no way I try can I get it to return the row's with a date.
>
>Thought you wanted without?
>
>>
>> I also can not search for a certain date. Whatever date format I use, I
>> either get no rows selected or an errror??
>> Is it possible to search by date?
>> Help!!
>> Andy
>
>Depends on your internal date format. Standard is DD-MON-YY.
>where request_date = to_date('21-OCT-99','DD-MON-YY');
>When you're not afraid of full table scans:
>where to_char(request_date,'your favourite format') = 'date in your favourite
>format';
>
Please Note That Oracle date's are stored as a number.

if you use sysdate to populate the date field then you will need to use

WHERE TRUNC(request_date) = TO_DATE('21-OCT-1999','DD-MON-YYYY')

the trunc will remove the time portion of the date

you may be better off using trunc on the update or insert if you want indexes work properly, or use a function index in 8I

>
>
>--
>Met vriendelijke groet/kind regards,
>
>Frank van Bortel
>Technical consultant Oracle
>
>Work: Home:
>---------------------------------- ----------------------------
>Inter Access V&L Hunzestraat 4
>Palatijn 3, 7521 PN Enschede 7555 WB Hengelo
>PoBox 545, 7500 AM Enschede (31)074-2425046
>053-4341500
>
>

-- 
Ralph Holland