Re: oracle date in where clause
- From: "sybrandb" <sybrandb@xxxxxxxxx>
- Date: 30 Mar 2007 01:18:55 -0700
On Mar 30, 8:28 am, zwadcut...@xxxxxxxxx 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
.
- References:
- oracle date in where clause
- From: zwadcutter
- oracle date in where clause
- Prev by Date: Re: O9i: general index question
- Next by Date: Passing connection to stored procedure
- Previous by thread: oracle date in where clause
- Next by thread: Passing connection to stored procedure
- Index(es):
Relevant Pages
|
