Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> oracle date in where clause

oracle date in where clause

From: <zwadcutter_at_yahoo.com>
Date: 29 Mar 2007 23:28:37 -0700
Message-ID: <1175236117.949619.142540@p15g2000hsd.googlegroups.com>


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 Received on Fri Mar 30 2007 - 01:28:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US