Re: oracle date in where clause

On Mar 30, 8:28 am, zwadcut..._at_yahoo.com wrote:
> I am having problems with a query where I want to update a certain
> date "holidays" say December 25 xmas always falls on the same day.
>
> --drop - create table
> drop table foo;
> create table foo (cola number, colb date, colc char(1));
>
> --populate table
> create sequence serial;
>
> DECLARE
> v_date DATE := TO_DATE ('12/31/1999', 'MM/DD/YYYY');
> BEGIN
> LOOP
> V_date := v_date+1;
> insert into foo values (serial.nextval,v_date,'N');
> exit when to_char(v_date, 'MM/DD/YYYY') ='12/31/2007';
> END LOOP;
> COMMIT;
> END;
> /
>
> I thought that this query would bring back all the Dec-25th's
>
> select count(*) from foo where colb=to_date('12-25','MM-DD');
>
> but it returns only one row. The row for 2007, this year. The only
> way I can find to return all the Dec-25th's is to alter the
> nls_date_format
>
> alter session set nls_date_format='MM-DD' ;
> select * from foo where colb like to_date('12-25','MM-DD');
>
> now all 8 rows are updated like I would expect but if you don't alter
> my session only one row is updated. It isn't honoring my date mask.
> Oracle 10gr2 my default date mask is 'MM-DD-YY'
>
> alter session set nls_date_format='MM-DD' ;
> 8 rows returned
> alter session set nls_date_format='MM-DD-YYYY' ;
> 1 row returned.
>
> is there a way to query this without the alter NLS_DATE_FORMAT and
> also use an = sign instead of the like.
>
> Z

For every to_date call Oracle will automagically take the parts of SYSDATE you don't specify.
So, in 2007, to_date('25-12','DD-MM') means to_date('25-12-2007','dd- mm-yyyy').
So your assertion is 'Oracle isn't honoring my date mask' simply isn't true, as you appear to be unaware of how to_date works. Obviously the correct query would have been select count(*) from foo where to_char(colb,'DD-MM') = '25-12'

This may result in a full table scan.

--
Sybrand Bakker
Senior Oracle DBA