Aug 122013
 
select unnecesary_conversions,
       can_lead_to_unexpected_results
from   your_queries
where  sysdate = to_date(sysdate);

no rows selected

As Tom Kyte often says, implicit conversions = evil. In this case putting a to_date 
around a date first converts it to a string, then converts it back to a date. If your 
session NLS settings don't include the full time, these will be lost in the conversion, 
effectively stripping these out of the query.

For the full script, keep reading.

create table your_queries (
  unnecesary_conversions integer,
  can_lead_to_unexpected_results integer
);

insert into your_queries values (1, 1);

commit;

-- we have no time component in the default 
-- formatting, so the to_date below applies 
-- a trunc() to sysdate in effect
alter session set 
  nls_date_format = 'DD-MON-YYYY';

select unnecesary_conversions,
       can_lead_to_unexpected_results
from   your_queries
where  sysdate = to_date(sysdate);

-- with the full datetime specified, we 
-- convert correctly so the query returns rows
-- this is set at the session level though, 
-- so you can't rely on this!
alter session set 
  nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select unnecesary_conversions,
       can_lead_to_unexpected_results
from   your_queries
where  sysdate = to_date(sysdate);

drop table your_queries purge;
Get SQLfail sent to your inbox

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>