Search code examples
oraclesql-navigator

SQL Navigator throws 'ORA-01834: Not a valid month' but query runs in other applications

I have stocked in this error many times but know I have no way to avoid and I have to get rid of it.

Sometimes I do run a query in SQL Navigator 6.1 XPert Edition and it throws:

ORA-01843: Not a valid month

But if I run this same query in same database but in other application(ie Aqua Data Studio) it works fine. It's just in isolated cases.

It may be some config problem?

EDIT: This query has that problem:

            select
                quantity dias_a_vencer
                , estab
                , initcap (planejador) planejador
                , atributo2 fabrica
                , mrp.item montagem
                , initcap (descricao) des_montagem
                , mrp.nro_docmto num_of
                , initcap (mrp.fornecedor) cliente
                , mrp.project_number projeto
                , initcap (comprador) processista
                , trunc (mrp.data_inicio) data_inicio
            from etlt_mrp_exceptions mrp
            where 
                mrp.compile_designator = 'ENGI'
                and mrp.dt_coleta > sysdate - 50
                and estab = '179'        -- PARAMETRO ESTAB FILTRO
                and atributo2 = '11'   -- PARAMETRO FABRICA FILTRO
                and nvl (mrp.quantity, 0) > 0
                and dt_coleta = '05/12/2011'                         -- parametro do grafico acima
                and initcap (planejador) = 'Maria Cristina Da Cruz Costa'        -- parametro do grafico acima
            order by quantity
            , des_montagem
Solution

  • To make your query fail-safe in all environments, you have to change this line:

    and dt_coleta = '05/12/2011'

    to 

    and dt_coleta = to_date('05/12/2011', 'DD/MM/YYYY')

    Assuming that you meant December 5th, and not May, 12th.

    Btw: what datatype are the columns estab and atributo2. If those are numbers you should remove the single quotes around the parameters. That is another "implicit" data conversion that would e.g. prevent the usage of an index on those columns.