Início / dba / Perguntas / 149997
Accepted
Sir Swears-a-lot
Asked: 2016-09-19 19:17:38 +0800 CST

A comparação de data na subconsulta falha com: "valor fora do intervalo"

  • 772

Eu tenho uma instrução de atualização que usa uma subconsulta para filtrar registros. Minha tabela de entrada contém datas em um campo varchar e algumas delas são inválidas.

CREATE TABLE [dbo].[input](
    [id] int,
    [START_DATE] [varchar](30) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(1, NULL) ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(2, '') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(3, '01 JUL 0201') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(4, '01 JUL 2016') ;

Nos dados de amostra, apenas o registro 4 é válido.

Minha consulta:

select a.[id] 
FROM (
    select [id], convert(datetime, [START_DATE],106) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0     
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < getdate()

Eu brinquei com várias cláusulas para excluir lixo:

  • len > 0 exclui nulos e ''.
  • Substring procura um ano sensato.
  • isdate verifica novamente se há uma data válida.

Tenho certeza de que existem outras maneiras mais eficientes de fazer isso, mas o ponto é que a subconsulta é executada e retorna registros válidos corretamente. neste caso, id = 4. Que tem uma data válida em 1º de julho de 2016.

A consulta externa deve simplesmente comparar a data convertida com a data de hoje. Mas em vez disso eu recebo:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Por quê? O que estou perdendo?

A consulta interna filtrou e converteu o valor que estou avaliando.

Existe uma maneira melhor de filtrar essa lista para ser usada em uma consulta de atualização?

sql-server-2008 t-sql

1 respostas

  1. Best Answer

    A ordem de execução depende muito do otimizador de consulta. Portanto, no seu caso, mesmo que o código SQL que você escreveu pareça que a subconsulta filtraria as datas inválidas primeiro, a aparência da execução real da consulta pode ser muito diferente.

    Você pode executar sua consulta com a opção (forçar ordem), mas tenha cuidado com essa, muito cuidado, e saiba o que realmente está fazendo. Outra opção seria uma tabela temporária, para pré-selecionar as datas válidas ou inválidas, mas isso também poderia ser muito caro.

    Embora pareça sujo, e é, você também pode comparar valores de string, para não se deparar com esse problema, apenas certifique-se de usar uma página de código que classificará como uma string como faria como uma data

    select a.[id] 
FROM (
    select [id], convert(varchar(23), convert(datetime, [START_DATE],106),121) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0     
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < convert(varchar(23, getdate(), 121)

    em suma, seria melhor salvar start_date como um tipo de data real.

    • 4

relate perguntas