Problema com formatação de data

[rsalbano]

Bom dia, pessoal

Estou com o seguinte problema:
Aqui na empresa usamos o BI da SAP (Business Object) e estamos trocando a base do SQL Server para Oracle e estou com seguinte problema:
Ao gerar uma consulta no BI, ele gerar as datas assim:

Selecionar tudo

BETWEEN  '01-07-2012 00:00:00'  AND  '31-07-2012 00:00:00

, sempre neste formato: DD-MM-YYYY HH:MI:SS". Na base os campos são do tipo date, mas quando vou executar uma consulta no BI o mesmo não retorna nada, pois os formatos estão diferentes. O que eu posso fazer? Eu não tenho como alterar o formato da ferramenta. já tentei converter o campo assim:

Selecionar tudo

select nome, AD001_data from contratos
where to_date(TO_CHAR(AD001_data, 'DD/MM/YYYY'), 'DD-MM-YYYY')  BETWEEN  '01-07-2012 00:00:00'  AND  '31-07-2012 00:00:00';

ou

Selecionar tudo

select nome, AD001_data from contratos
where to_date(AD001_data, 'DD/MM/YYYY') BETWEEN  '01-07-2012 00:00:00'  AND  '31-07-2012 00:00:00';

ou

Selecionar tudo

select nome, AD001_data from contratos
where to_date(AD001_data, 'DD-MM-YYYY') BETWEEN  '01-07-2012 00:00:00'  AND  '31-07-2012 00:00:00';

Quando executo no Oracle aparece o seguinte erro:

Selecionar tudo

Erro de SQL: ORA-01830: a imagem do formato da data termina antes de converter a string de entrada inteira
01830. 00000 -  "date format picture ends before converting entire input string"

Se eu retirar o 00:00:00, funciona.

Desde já agradeço, a ajuda.

[rogenaro]

O campo AD001_data já é do tipo date, logo o to_date deve ser realizado para os parâmetros informados:

Selecionar tudo

select nome, AD001_data from contratos
where AD001_data BETWEEN to_date('01-07-2012 00:00:00', 'dd-mm-yyyy hh24:mi:ss') AND to_date('31-07-2012 00:00:00', 'dd-mm-yyyy hh24:mi:ss');

[rsalbano]

Olá,

Eu não posso alterar o que esta depois do between. Esta parte é a ferramenta que insere. Eu consigo ajustar o que esta antes do between.

[rsalbano]

tentei colocar assim:

Selecionar tudo

to_date(to_char(AD001_data, 'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss') BETWEEN  '01-07-2012 00:00:00'  AND  '31-07-2012 00:00:00'

Mas apresenta o mesmo erro.
Vale salientar que o período de 01-07 a 31-07, é só um exemplo. Este período o usário escolhe.

[DanielNN]

O campo AD001_data já é do tipo date, logo o to_date deve ser realizado para os parâmetros informados:

Selecionar tudo

select nome, AD001_data from contratos
where AD001_data BETWEEN to_date('01-07-2012 00:00:00', 'dd-mm-yyyy hh24:mi:ss') AND to_date('31-07-2012 00:00:00', 'dd-mm-yyyy hh24:mi:ss');

Tu chegou a fazer este teste???

[rogenaro]

Sem alterar o parâmetro fica meio impossível, não há garantia nenhuma de que o between vai comparar uma string corretamente, principalmente neste formato.

Se não é possível *mesmo* alterar os parâmetros (acho meio dificil uma ferramenta deste tipo passar datas como string para o banco, sem opção de parametrização), a única alternativa seria alterar o formato da data na sessão ou até mesmo no banco para este formato passado pela aplicação ('dd-mm-yyyy hh24:mi:ss').
Neste caso não seria necessário realizar nenhuma conversão

Selecionar tudo

select nome, AD001_data from contratos
where AD001_data BETWEEN '01-07-2012 00:00:00' AND '31-07-2012 00:00:00';

Mas adianto que seria uma solução horrível, e assim que houver qualquer alteração nestes parâmetros no banco ou na sessão nada mais vai funcionar...

Não é possível sequer inverter o parâmetro de entrada informado pelo usuário, para o formato 'yyyymmddhh24miss'? Pelo menos seria possível testar as datas como number:

Selecionar tudo

select nome, AD001_data from contratos
where to_number( to_char( AD001_data, 'yyyymmddhh24miss' ) ) BETWEEN '20120701000000' AND '20120731000000' ;

Neste caso pelo menos a comparação funcionaria.