First up, a couple of things:
- Oracle Database has no "day" data type. Dates always include the time too. You can effectively strip the time from a date using:
trunc(dt)
But this is really a date with the time of midnight.
- The NLS settings only affect display and implicit conversions. As long as you're comparing dates to dates, it doesn't matter what these are.
Notice how the code below selects a row from the table, regardless of the NLS date format:
create table t (
d date
);
declare
dt date;
procedure count_matching as
ct pls_integer;
begin
select count(*) into ct
from t where d = dt;
dbms_output.put_line('Found ' || ct || ' for date ' || dt);
end count_matching;
begin
dt := sysdate;
insert into t values (dt);
execute immediate q'|alter session set nls_date_format = 'yyyy-mm-dd'|';
count_matching();
execute immediate q'|alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss'|';
count_matching();
end;
/
Found 1 for date 2017-11-13
Found 1 for date 13-NOV-2017 03:12:09So:
1. If you have columns with specific times and you pass a "day" (a date with time of midnight), to find these rows you should:
- Check the column is greater than or equal to the parameter AND
- it's strictly less than the parameter plus one day
e.g.:
where register_date >= ai_registerdate and register_date < ai_registerdate + 1
You could trunc() both the parameter and column. But this affects your indexes. More on that below.
2. Both are "wrong"!
The first will have implicit conversions. The second does an unnecessary conversion from a date to a string.
Assuming ai_registerdate has the data type date, all you need is:
where register_date = ai_registerdate
If it's a string instead, you should to_date the parameter as appropriate.
where register_date = to_date(ai_registerdate, 'fmt mask')
Applying a function to the column stops the optimizer using an index on that column. So if you have
create index i on t (dt);
And a query:
where to_date(dt) = ...
You'll get a full table scan instead of an index range scan. So generally you want to avoid functions on your columns in the where clause. If you do apply a function in your index, you'll want to use the exact same function in your SQL.
You can read more about how indexes work in this article:
https://blogs.oracle.com/sql/how-to-create-and-use-indexes-in-oracle-database#choose Search for "Function-based Indexes" for more discussion on this.