oracle date in where clause
- From: zwadcutter@xxxxxxxxx
- Date: 29 Mar 2007 23:28:37 -0700
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
.
- Follow-Ups:
- Re: oracle date in where clause
- From: sybrandb
- Re: oracle date in where clause
- Prev by Date: Re: utl_smtp -> meeting request
- Next by Date: Re: empty string or null
- Previous by thread: Getting an Application to run with both 8i and 10g
- Next by thread: Re: oracle date in where clause
- Index(es):
Relevant Pages
|
|
