SV: comparing datetime
Date: Thu, 18 Apr 2013 07:49:48 +0000
Message-ID: <3967181ED595B445B1E39718204FD4BD13C9B902_at_SRVEXC5.dst.local>
Slightly off-topic: Why not use timestamp literals. TIMESTAMP '1997-01-31 09:26:56.66 +02:00'. Standard SQL, cleaner syntax. http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51062 /Niels
-----Oprindelig meddelelse-----
Fra: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] På vegne af Mark W. Farnham
Sendt: 18. april 2013 08:27
Til: oracle-l_at_freelists.org
Emne: RE: comparing datetime
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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 18 2013 - 09:49:48 CEST