Re: comparing datetime
Date: Thu, 18 Apr 2013 11:08:35 +0100
Message-ID: <3703752558423455569_at_unknownmsgid>
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_at_sferacarta.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of jo
> Sent: Thursday, April 18, 2013 2:50 AM
> Cc: oracle-l_at_freelists.org
> 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_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?
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 18 2013 - 12:08:35 CEST