The '=>' syntax (named notation) requires 11g (11.1 onwards) to work within SQL queries, hence the syntax error in 10.2.0.5. On 18 Apr 2013, at 10:27, jo <jose.soares@xxxxxxxxxxxxxx> wrote: Hi Mark, I choose to use CAST instead of to_timestamp because I'm using sqlalchemy to access even other databases using the same code like postgres for example and to_timestamp doesn't work on postgres thus I must to remain compatible. the query that you sent me give me this error: SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')) * ERROR at line 1: ORA-00907: missing right parenthesis this is the version: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi anyway I don't have privileges as administrator to that db. ------------------------ I don't know if it depends from the db version or any other thing. :-\ because I tried the SELECT ts_ultima_modifica FROM movimento_canina WHERE ts_ultima_modifica='2013-04-11 10:14:52.782680'; (without CAST) on another oracle database Oracle Database 11g Release 11.2.0.1.0 - 64bit Production and it works correctly but select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); plan_table_output ----------------------------------------- User has no SELECT privilege on V$SESSION (1 rows) j Mark W. Farnham wrote: > You really don't want to run the function on the column for each row. You > want to get the timestamp literal either with the to_timestamp function with > the flexible formatting options or in the form the other fellow suggested. > > Unless you're trying to avoid using an index on the timestamp column or feel > a need to give your cpu a little extra exercise, then cast your column all > you want, but that is going to do extra work on each row, which is a bad > habit that can be significant. > > What release are you on that dbms_xplan.display_cursor is not working? > Perhaps you need to enable something. Good lord, I take that being available > as a given and I don't know how people live without it. > > mwf > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of jo > Sent: Thursday, April 18, 2013 2:50 AM > Cc: oracle-l@xxxxxxxxxxxxx > Subject: Re: comparing datetime > > Hi Mark, > > 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@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? >> -- //www.freelists.org/webpage/oracle-l