between dates function in sql

I think the question is not very clear but I feel the answer by paquicuba is meant to solve it!!

Just an alternative to this solution, you can use this also,

AND (pol.date_entered = To_Date ('01/10/2005', 'dd/mm/yy')  OR pol.date_entered = To_Date ('10/10/2005', 'dd/mm/yy'))

Sorry.. I meant..

If you want the dates to include Oct 1 and Oct 10, then change to

AND pol.date_entered BETWEEN To_Date ('01/10/2005', 'dd/mm/yy') AND To_Date ('10/10/2005 23:59:59', 'dd/mm/yy hh24:mi:ss')

If you want Oct1 and Oct 10 excluded change to

AND pol.date_entered BETWEEN To_Date ('01/10/2005', 'dd/mm/yy')+1 AND To_Date ('10/10/2005', 'dd/mm/yy')

Assuming that the column: pol.date_entered has a data type of: date, and that the actual values in that column include the time of day, then awking00 is correct.  That is, Oracle date columns and variables include a time portion, so when you ask Oracle to return values that are between the dates of: 1-Oct-2005 and 10-Oct-2005, Oracle assumes a time of midnight for both of those dates (since you didn't provide a different time portion) and records that were entered on the 10th, but after midnight (which is probably most or all records for that day) are excluded.  You have to use either the extended date/time format that awking00 provided to include the records for later times on the 10th, or if you think there are no records that have a time value of exactly midnight, you could use:
 
and pol.date_entered BETWEEN To_Date ('01/10/2005', 'dd/mm/yy') AND To_Date ('11/10/2005', 'dd/mm/yy')

That would actually get the records from 1-Oct-2005 (any time of day) through the end of day: 10-Oct-2005.  Be careful with this approach though, since if there any records for exactly midnight on the 11th, they would also be included.

Or you could use two separate conditions instead of "between" like this:
and pol.date_entered >= To_Date ('01/10/2005', 'dd/mm/yy')
AND pol.date_entered < To_Date ('11/10/2005', 'dd/mm/yy')

Note that you have to use ">=" for the minimum date (in case there are records for exactly midnight on that day) and "<" for the date one date beyond the date you want included.

DeanHorak,
I think I already said that.

markgeer,
>>Be careful with this approach though, since if there any records for exactly midnight on the 11th, they would also be included.<<
That's why I suggested ...AND to_date('10/10/05 23:59:59','dd/mm/yy hh24:mi:ss')

DarrenJackson,
Actually, I would suggest using ...AND to_date('10/10/2005 23:59:59','dd/mm/rrrr hh24:mi:ss') to avoid any 1905 vs. 2005 type of issues.

Hi,

I have read the posts but after a few ones lost track and intrest. I have ur query modified below try it and let me know if your problem is solved.

========================================================================================================

SELECT  pol.order_no,
                pol.project_id Project,
                po.authorize_code Coordinator,
                pol.line_no Line,
                pol.description,
                po.vendor_no Supplier_ID,
                s.vendor_name Supplier,
                pol.date_entered Order_Date,
                pol.wanted_delivery_date Wanted_Delivery_Date,
                pol.original_qty * pol.fbuy_unit_price Line_Price,
                pol.state,
                po.note_id,
                substr( ifsapp.Document_Text_API.Get_All_Notes(po.note_id,'6'),1,2000) Text
FROM ifsapp.purchase_order po,
     ifsapp.purchase_order_line_all pol,
     ifsapp.supplier s
WHERE pol.order_no = po.order_no
AND s.vendor_no = po.vendor_no
--AND pol.line_no = '1'
AND pol.project_id = '90766'
AND trunc(pol.date_entered) BETWEEN To_Date ('01/10/2005', 'dd/mm/yyyy') AND To_Date ('12/10/2005', 'dd/mm/yyyy')
ORDER BY pol.order_no ASC, pol.line_no ASC
==================================================================================================

2 changes here
1. change the yy to yyyy
2. use trunc function in pol.date_entered - trunc will give you only the date part in case the data also stores timestamp.

HTH

makhan