RE: comparing datetime

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 18 Apr 2013 02:26:49 -0400
Message-ID: <00e101ce3bfd$b5fe9c50$21fbd4f0$_at_rsiz.com>

If you want an explicit conversion of a constant to a timestamp, use to_timestamp:

to_timestamp('2012-03-13 14:12:14.476865','YYYY-MM-DD HH24:MI:SS.FFTZD')

If you want to see what your query is using for an implicit timestamp conversion, then run your query and then run

select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

and you should see the comparison actually being done in a filter statement.

Then, seeing what Oracle is doing might help you see what is wrong.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jose Soares
Sent: Wednesday, April 17, 2013 8:59 AM
To: oracle-l_at_freelists.org
Subject: comparing datetime

Hi all,

I'm having some troubles comparing datetimes in oracle... what's wrong with these queries:

file: login.sql

alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FFTZD';

select codice_bdn,data_invio_entrata from movimento_canina join

tipo_movimento_canina on 
tipo_movimento_canina.codice=cod_tipo_movimento_entrata and 
data_invio_entrata>'2012-03-13 14:12:14.476865' and rownum = 1;

select codice_bdn,data_invio_entrata from movimento_canina join

tipo_movimento_canina on 
tipo_movimento_canina.codice=cod_tipo_movimento_entrata and 
data_invio_entrata='2012-03-13 14:12:14.476865' and rownum = 1;

select codice_bdn,data_invio_entrata from movimento_canina join

tipo_movimento_canina on 
tipo_movimento_canina.codice=cod_tipo_movimento_entrata and 
data_invio_entrata<'2012-03-13 14:12:14.476865' and rownum = 1;

$ sqlplus uri

Session altered.

CODI DATA_INVIO_ENTRATA

---

---

0 2012-03-13 14:12:14.476865

no rows selected

no rows selected

SQL> Why data_invio_entrata > '2012-03-13 14:12:14.476865' if they contains the same value?
j

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 18 2013 - 08:26:49 CEST