CODEWITHSUNDEEP
databaseoracle-database
Tianyun Ling
Tianyun Ling

Reputation: 1097

Oracle where clause date selection does not work

Basically, my problem can be re-created using the following script in oracle db:

create table test
(
current_date date
);

insert into test(current_date) values(  TO_DATE('2018-02-01', 'yyyy-MM-dd') ); 
insert into test(current_date) values(  TO_DATE('2018-03-01', 'yyyy-MM-dd') ); 
insert into test(current_date) values(  TO_DATE('2018-04-01', 'yyyy-MM-dd') ); 

--select data later than May
select * from test where  current_date >=  TO_DATE('2018-05-01', 'yyyy-MM-dd') ;

But all three date come out as result? Why? Did I do something wrong here?

2/1/2018 12:00:00 AM
3/1/2018 12:00:00 AM
4/1/2018 12:00:00 AM

Upvotes: 1

Views: 38

Answers (1)

APC
APC

Reputation: 146219

It's because current_date is an Oracle built-in function, returning the current date (and time). The way Oracle namespaces work means the built-in reference trumps your column name.

One way to fix it would be to use a table alias in your query:

 select * from test t 
 where  t.current_date >=  TO_DATE('2018-05-01', 'yyyy-MM-dd') ;

This tells Oracle you're referencing the column name not the built-in.

Obviously the better solution is to change your table so you don't have a column name which clashes with an Oracle built-in.

Upvotes: 3

Related Questions