Re: comparing datetime

I resolved the question by using CAST...

SELECT ts_ultima_modifica FROM movimento_canina WHERE CASTcast(ts_ultima_modifica AS TIMESTAMP)='2013-04-11 10:14:52.782680';

thanks for the help anyway.

ps:

by the way, the query
select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); doesn't work.

Mark W. Farnham wrote:
> 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
>
>
>

-- 
Jose Soares Da Silva                     _/_/
Sferacarta Net
Via Bazzanese 69                       _/_/    _/_/_/
40033 Casalecchio di Reno             _/_/  _/_/  _/_/
Bologna - Italy                      _/_/  _/_/  _/_/
Ph  +39051591054              _/_/  _/_/  _/_/  _/_/
fax +390516131537            _/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com        _/_/_/      _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

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