To everyone who has replied, 1. not a cast issue 2. not a time trunc(date) issue 3. not what you think it is: the answer lies in current_date as a variable, select current_date from dual; so even if you set a variable called current_date and assign a value to it, when sql executes its completely ignored. took me a while to see it. joe _______________________________________ Joe Testa, Oracle Certified Professional Senior Consultant Data Engineering and Administration Nationwide Investments (Work) 614-677-1668 (Cell) 614-312-6715 Interested in helping out your marriage? Ask me about "Weekend to Remember" Dec 11-13, 2009 here in Columbus. From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx> To: oracle-l@xxxxxxxxxxxxx Date: 04/10/2009 11:45 AM Subject: Re: fun debugging stuff on a friday Sent by: oracle-l-bounce@xxxxxxxxxxxxx Hey Joe, The things that jump out at me here are the huge assumptions that Oracle will always cast VARCHAR2 to or from DATE the same and that your locale date format will also never change. Explicit casting and formatting is a much safer way to code, IMHO: current_date := TO_DATE('30-OCT-07','DD-MON-RR'); ... WHERE EFFECTIVE_DATE = TO_DATE('30-OCT-07','DD-MON-RR'); Be explicit! Implicit expectations/assumptions like this are showstoppers in database upgrades. My Friday $.02, Rich > I get this code below, first glance says, this should work, Oracle is so > much like C, gives you a gun, ammo, takes off the safety and has the > trigger most of the way pulled while pointing the gun at your foot and > assists you in pulling the trigger the rest of the way. > > > > > > declare > current_date date; > ctr number; > > begin > > current_date:='30-OCT-07'; > > > SELECT count(distinct SECURITY_ALIAS) > into ctr > FROM DATAMARTDBO.SECURITY_DETAILS > WHERE EFFECTIVE_DATE = current_date; > > dbms_output.put_line('CTR1:'||ctr); > > > > SELECT count(distinct SECURITY_ALIAS) > into ctr > FROM DATAMARTDBO.SECURITY_DETAILS > WHERE EFFECTIVE_DATE = '30-OCT-07'; > > dbms_output.put_line('CTR2:'||ctr); > > end; > / > > CTR1: 0 > CTR2: 35178 -- //www.freelists.org/webpage/oracle-l