RE: comparing datetime
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Apr 2013 02:26:49 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jose Soares
Sent: Wednesday, April 17, 2013 8:59 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
