Alternativa de resolução para o erro “The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.”

Publicado: 10 de março de 2012 em SQL Server, Virtual PASS BR
Tags:

Olá pessoal, é comum tentarmos importar dados de arquivos TXT, Excel, etc. para nossa base de dados, no entanto, estas fontes de dados podem não conter os dados corretos conforme o campo que estamos utilizando na nossa tabela no SQL Server.

Um caso típico é quando tentamos importar linhas de arquivos TXT que representam dados como Data ( Datetime, SmallDatetime ) em tabelas do SQL Server. Um problema que pode ocorrer aqui é que alguns dados do TXT podem conter datas inválidas ( algo como 00-00-0000, 20121313, etc. ) e isso iria gerar uma excessão em nossa importação.

Este tipo de excessão pode ser tratado de diversas formas ( Try-Catch, uso do SSIS, etc. ), e dependendo da regra de negócio pode ser que seja necessário abortar a importação inteira ou simplesmente tratar o dado com erro e gerar um log dos registros “problemáticos”.

Para este exemplo, vou levar em consideração que a regra de negócio seja importar os registros que possuam datas inválidas e setar o valor NULL nas mesmas, bem como, ao invés de arquivo TXT farei a importação de uma tabela que possui os dados em campo varchar para outra que tem o campo SmallDatetime.

Segue abaixo script de exemplo:

USE master
GO
if DB_ID(‘db_valida_data’) IS NOT NULL
    DROP DATABASE db_valida_data

GO
–Criação da base de teste
CREATE DATABASE db_valida_data
GO
USE db_valida_data
GO
–Criação da tabela de origem
CREATE TABLE tb_teste(
    id INT PRIMARY KEY IDENTITY(1,1),
    data varchar(8)
)
GO

–Insiro alguns registros de teste
INSERT INTO tb_teste select ‘20120101’
INSERT INTO tb_teste select ‘20120501’
INSERT INTO tb_teste select ‘20120107’
INSERT INTO tb_teste select ‘20120109’

–Estes dois registros possuem data inválida
INSERT INTO tb_teste select ‘00000000’
INSERT INTO tb_teste select ‘12345678’
GO

–Crio a tabela que receberá os dados importados

CREATE TABLE tb_datas(
    id INT PRIMARY KEY IDENTITY(1,1),
    data smalldatetime
)
GO
–Tento importar os dados. No código abaixo, o seguinte erro será exibido:
–The conversion of a varchar data type to a smalldatetime data type resulted in an outof-range value.
–The statement has been terminated.
INSERT INTO tb_datas(data)
select data from tb_teste
GO
–Uma alternativa para resolução do problema seria o uso da função ISDATE (http://msdn.microsoft.com/pt-br/library/ms187347.aspx)

INSERT INTO tb_datas(data)
SELECT
    CASE
        WHEN ISDATE( data ) = 1 THEN
             data
        ELSE
             NULL
    END
FROM tb_teste
GO

–Exibindo os dados importados
SELECT * FROM tb_datas

É isso ai pessoal.

Grande abraço,
Demétrio Silva

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s