Oracle: não é um mês válido

Eu tenho uma tabela com os seguintes campos:

Relatórios (nome da tabela) Rep_Date (date) Rep_Time (date)

O campo Rep_Time tem valores como '01 / 01/1753 07:30:00 ', ou seja, a parte do tempo é relevante. Eu escrevi a seguinte consulta:

select Reports.pid, MaxDate from Reports
INNER JOIN (
    select pid, max(TO_DATE(TO_CHAR(REP_DATE, 'DD/MM/YYYY')
        || TO_CHAR(REP_TIME, 'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS')) As MaxDate
    from reports
    group by pid
) ReportMaxDate
on Reports.PID = ReportMaxDate.PID
AND To_Date(To_Char(MaxDate, 'DD/MM/YYYY')) = REP_DATE
WHERE REPORTS.PID=61

A parte da tabela derivada da consulta é executada, mas quando executo a consulta inteira, recebo um erro: "not a month válido". Por que é isso?

Para ajudar a depurar isso; se eu executar a seguinte consulta:

select rep_date, rep_time from reports where pid=61 and rownum=1

Eu recebo:

Rep_Date = 01/04/2009
Rep_Time = 01/01/1753 13:00:00

ATUALIZAÇÃO 15:58 Agora posso executar a seguinte consulta:

select Reports.pid, MaxDate from Reports
INNER JOIN (
    select pid, max(TO_DATE(TO_CHAR(REP_DATE, 'DD/MM/YYYY')
        || TO_CHAR(REP_TIME, 'HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS')) As MaxDate
    from reports group by pid
) ReportMaxDate
on Reports.PID = ReportMaxDate.PID
AND to_date(to_char(maxdate,'MM/DD/YYYY'),'MM/DD/YYYY') = REP_DATE
WHERE REPORTS.PID=61

No entanto, preciso adicionar mais uma instrução aoWHERE cláusula comparando a parte do tempo de MaxDate para rep_time:to_date(to_char(maxdate,'MM/DD/YYYY'),'MM/DD/YYYY') = REP_DATE não funciona.

questionAnswers(2)

insira um registro usando sysdate. pelo você pode encontrar o formato de data de actuel. por exemplo

insert into emp values(7936,'Mac','clerk',7782,sysdate,1300,300,10);

Agora, selecione o registro inserido.

select ename,hiredate from emp where ename='Mac';

o resultado é

ENAME   HIREDATE
Mac     06-JAN-13

voila, agora seu formato de data ativo é encontrado.

QuestionSolution

1

To_Date(To_Char(MaxDate, 'DD/MM/YYYY')) = REP_DATE

está causando o problema. quando você usa to_date sem o formato de hora, o oracle usará o formato NLS das sessões atuais para converter, que no seu caso pode não ser "DD / MM / AAAA". Verifique isso ...

SQL> select sysdate from dual;

SYSDATE
---------
26-SEP-12

Which means my session's setting is DD-Mon-YY

SQL> select to_char(sysdate,'MM/DD/YYYY') from dual;

TO_CHAR(SY
----------
09/26/2012


SQL> select to_date(to_char(sysdate,'MM/DD/YYYY')) from dual;
select to_date(to_char(sysdate,'MM/DD/YYYY')) from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') from dual;

TO_DATE(T
---------
26-SEP-12

2

Mais importante, por que você está convertendo para char e depois para data, em vez de comparar diretamente

MaxDate = REP_DATE

Se você quiser ignorar o componente de tempo no MaxDate antes da comparação, você deve usar ..

trunc(MaxDate ) = rep_date

em vez de.

== Atualização: com base na pergunta atualizada.

Rep_Date = 01/04/2009 Rep_Time = 01/01/1753 13:00:00

Eu acho que o problema é mais complexo. Se rep_time se destina a ser apenas a hora, então você não pode armazená-lo no banco de dados como uma data. Teria que ser uma string ou data para intervalo de tempo ou numericamente como segundos (graças a Alex, vejaisto). Se possível, sugiro usar uma coluna rep_date que tenha data e hora e compare diretamente com a coluna de data máxima.

Se for um sistema em execução e você não tiver controle sobre repdate, tente isso.

trunc(rep_date) = trunc(maxdate) and 
to_char(rep_date,'HH24:MI:SS') = to_char(maxdate,'HH24:MI:SS')

De qualquer forma, o tempo está sendo armazenado incorretamente (como você pode dizer a partir do ano de 1753) e poderia haver outros problemas no futuro.

 Rajesh Chamarthi26 de set de 2012 17:17
editei minha resposta
 w005197726 de set de 2012 16:48
+1, obrigado, funciona. Você poderia explicar como obter o componente de tempo de um datetime antes de aceitar? to_date (to_char (maxdate, 'HH24: MI: SS'), 'HH24: MI: SS') não parece funcionar.
 Rajesh Chamarthi26 de set de 2012 16:57
A parte to_char está funcionando em sua consulta. O problema é que você está tentando converter apenas o horário (10:55:30 por exemplo) em uma data. Como Se você quer apenas armazenar tempo, você terá que fazê-lo em um tipo de dados string (varchar2).
 w005197726 de set de 2012 17:00
Por favor, veja a revisão da minha pergunta.
 w005197726 de set de 2012 16:34
Em resposta a sua pergunta, MaxDate é um DateTime e REP_DATE é uma Data. O que trunc (MaxDate) = rep_date DO?
 Alex Poole26 de set de 2012 17:12
@RajeshChamarthi - apenas para ser pedante, um tempo em si nãoter para ser uma string, ela pode ser armazenada numericamente como segundos oucomo um intervalo, ou uma hora em uma data fixa (que o OP já parece estar fazendo; usando 1753 é curioso, mas então está usando campos separados de data e hora em primeiro lugar). Todos têm implicações de conversão.
 Rajesh Chamarthi26 de set de 2012 17:20
@Alex: Obrigado. totalmente certo.
 Rajesh Chamarthi26 de set de 2012 16:38
trunc (date) removeria o componente de tempo (na verdade, ele torna o componente de tempo zero, que é o caso de uma coluna de data como REP_DATE. Não existe apenas data no Oracle.)

yourAnswerToTheQuestion