Oracle Date

Hello,
I have the below query:
select distinct
	po.c_order_id 						    as source_pohdr_id,	
    'WYOMING'                               as source_system,
	v.vendor_code							as vendor_code,
	v.name									as VENDOR_NAME,
    trim(po.em_sc_program_code)             as PROGRAM_CODE,
	PGM.DESCRIPTION 						AS PROGRAM_NAME,
	substr(ltrim(po.documentno),1,12)  		as PO_DOCUMENTNO,
	cast(po.totallines	as NUMBER(18,0))	as totallines,
	po.grandtotal							as grandtotal,
	po.salesrep_id                          as source_person_id,
	PER.NAME 								AS BUYER_NAME,
    'WYOMING'                               as source_system,
	PO.DATEPRINTED 					as PRINTED_DATE,
	PO.CREATED 						AS CREATED_DATE
, POC.ORDER_TYPE 
, POC.AGREEMENT_INDICATOR 
, POC.SOURCE_JUSTIFICATION  
, POC.PRICE_ANALYSIS
, POC.CONTRACT_PROVISIONS 
, POC.GOVERNMENT_PROPERTY  
, POC.SUBCONTRACTING_PLAN  
, POC.PRIOR_CONSENT_REQUIREMENTS  
, POC.AUDIT_STATUS_SYMBOL  
, POC.PO_SUBCONTRACT_CLOSEOUT 
from c_order po
  left join sc_vendor v on ( po.c_bpartner_id = v.c_bpartner_id )
	LEFT join SC_CUSTOMER SF on PO.ad_org_id = SF.ad_org_id and SF.FRONT_END = 'STORES' 
	LEFT join SC_CUSTOMER C ON C.C_BPARTNER_ID = PO.C_BPARTNER_ID
	LEFT JOIN SC_PO_CLASSIFICATION POC ON po.C_ORDER_ID = POC.C_ORDER_ID
	LEFT JOIN sc_program PGM   ON TRIM(po.em_sc_program_code) = PGM.PROGRAM_CODE
	LEFT JOIN SC_PERSON PER ON PO.SALESREP_ID = PER.SC_PERSON_ID
WHERE 
po.issotrx = 'N' and PO.GRANDTOTAL>=3000
  	--and to_char(po.dateprinted,'MM-DD-YYYY')  between '01-31-2003' and  '07-03-2003'  
    AND  to_char(po.dateprinted,'MM-DD-YYYY')>= '01-02-2003' and to_char(po.dateprinted,'MM-DD-YYYY') <= '01-31-2003'       
    AND trim(po.em_sc_program_code) not in('XXX','U495','U181','A607')

Open in new window

The above code should pull rows which have printeddate in 1/31/2003 to 7/3/2003.But for some reason its pulling from all the date ranges years available in that table.Not sure how to handle dates in oracle where clause.
The table has date data like '06-FEB-08' and the user will pass parameter like mm-dd-yyyy format.
Please help.
Star79Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>The table has date data like '06-FEB-08'

Oracle dates do not have a format until displayed.  The format you 'see' is the default format to display dates.

Don't convert dates to strings for compares.

compare dates to dates:
AND po.dateprinted>= to_date('01-02-2003','MM-DD-YYYY') and po.dateprinted < to_date('01-31-2003','MM-DD-YYYY')+1

Notice the +1 at the end...  It goes with the <

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.