Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> oracle date in where clause
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;
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