Поиск некорректных дат средствами SQL

Ошибка «Дата ‘ **.**.**** ‘ не может быть записана в базу данных на MS SQL Server с нулевым смещением дат.

Собственно описание проблемы:

Обратился ко мне мой старый товарищ, с просьбой посодействовать в поиске одной ошибке, которая у него возникала при создании распределенной базы данных. Текст ошибки приблизительно такой:

Дата ‘01.01.0001 00:00:00’ не может быть записана в базу данных на MS SQL Server с нулевым смещением дат.

Задача стояла в следующем, чтоб пройтись по всем таблицам базы и найти, где накосячили доблестные работницы финансовой службы.

Решение:

1. Выполняем на SQL следующий код:

SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @sql_str varchar(1000);
DECLARE @WrongData varchar(15);
DECLARE @CountRec Int;

DECLARE tables CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

OPEN tables;


-- кривая дата
SELECT @WrongData = '01.01.0001 00:00:00';

FETCH NEXT
FROM tables
INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN


----  проверка на дату в документах  и журналах
IF col_length(N''+@tablename,'_Date_Time') is not null

BEGIN
SELECT @sql_str = 'SELECT * INTO _ResWrongDate FROM ' + @tablename + ' WHERE _Date_Time<'+''''+@WrongData+''''+'';
EXEC(@sql_str)

SELECT @CountRec = (SELECT COUNT(*) FROM _ResWrongDate);

IF @CountRec > 0
BEGIN
PRINT('Неккоректная дата в таблице '+@tablename+' . записей '+ LTrim(Str(@CountRec)));
END

DROP TABLE _ResWrongDate;
END;

-- в регистрах
IF col_length(N''+@tablename,'_Period') is not null

BEGIN
SELECT @sql_str = 'SELECT * INTO _ResWrongDate FROM ' + @tablename + ' WHERE _Period<'+''''+@WrongData+''''+'';
EXEC(@sql_str)

SELECT @CountRec = (SELECT COUNT(*) FROM _ResWrongDate);

IF @CountRec > 0
BEGIN
PRINT('Неккоректная дата в регистре '+@tablename+' . записей '+ LTrim(Str(@CountRec)));
END

DROP TABLE _ResWrongDate;
END;

FETCH NEXT
FROM tables
INTO @tablename;
END;


CLOSE tables;
DEALLOCATE tables;

GO

Получаем все названия таблиц в которых присутствует некорректная дата.

Либо обработкой, либо сами с помощью команды ПолучитьСтруктуруХраненияБазыДанных находим, что это за документ или регистр.

Находим этот объект, исправляем корректную дату.

В нашем случае дата выдачи паспорта человеку была аж 01.01.0001. Долгожитель попался 🙂

8 Comments

Филин 03.10.2019 at 17:14

А если проблемная дата будет не в «Периоде», а где-то еще? Лучше уж все колонки соответствующего типа проверить.

declare @table nvarchar(128),
@obj_id int,
@cmd nvarchar(max),
@condition nvarchar(max)


declare tbls cursor for
select name, object_id
from sys.tables

open tbls
while 1=1
begin
fetch next from tbls into @table, @obj_id
if @@FETCH_STATUS != 0
break

set @condition = N»
select @condition = @condition  + ‘( ‘+c.name+’ = »00010101 00:00:00» ) or’
from sys.columns c
inner join sys.types t
on c.user_type_id = t.user_type_id
where c.object_id = @obj_id
and  t.name in ( ‘datetime2’) —‘datetime’

if (len( @condition) = 0 ) or (@condition is null)
continue

set @condition = left (@condition, len(@condition) — 2)  —уберем последний OR

set @cmd = ‘if exists (select top 1 1 from ‘+@table+ N’ where ‘ +@condition + N’)
print (» ‘+@table+N»’)’

exec (@cmd)
end
close tbls
deallocate tbls

Показать

Reply ↓

sutygin 04.10.2019 at 09:34

Ну задачи колонки проверить небыло. К тому же явно выгрузка валится не из за реквизитов документов, а из за выгрузки движений регистра.

Reply ↓
Jimbo 04.10.2019 at 11:22

SQL2008R2 EngStd на типовых БП 3 и не типовой УПП:

Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The statement has been terminated.

Reply ↓
Fox-trot 04.10.2019 at 11:28

(2) таки да, валится ежели участвует в первичных ключах-индексах

Reply ↓
sutygin 04.10.2019 at 15:00

SELECT @WrongData = ‘01.01.0001 00:00:00’;

Вот в этой строке у вас не корректное значение.

Скорее у вас смещение дат

Поставьте SELECT @WrongData = ‘01.01.2001 00:00:00’;

Reply ↓
sutygin 04.10.2019 at 15:08

И да, если база со смещением дат, то к дате надо прибавлять 2000.

И проверять на некорректные значения лучше с даты к примеру ‘01.01.3919 00:00:00’;

Reply ↓
Jimbo 04.10.2019 at 15:38

(5) SELECT @WrongData = ‘2001-01-01 00:00:00’; помогло

Reply ↓
Jimbo 04.10.2019 at 15:40

(7) ну и DECLARE @WrongData varchar(19); а не 15

Reply ↓

Поиск некорректных дат средствами SQL

Related Posts

8 Comments

Leave a Comment Отменить ответ