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

Публикация № 1130639

Администрирование - Администрирование данных 1С - Поиск данных

Ошибка "Дата ' **.**.**** ' не может быть записана в базу данных на 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. Долгожитель попался :) 

Специальные предложения

Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Филин 146 03.10.19 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


Показать
2. sutygin 36 04.10.19 09:34 Сейчас в теме
Ну задачи колонки проверить небыло. К тому же явно выгрузка валится не из за реквизитов документов, а из за выгрузки движений регистра.
4. Fox-trot 118 04.10.19 11:28 Сейчас в теме
(2) таки да, валится ежели участвует в первичных ключах-индексах
3. Jimbo 7 04.10.19 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.
5. sutygin 36 04.10.19 15:00 Сейчас в теме
SELECT @WrongData = '01.01.0001 00:00:00';
Вот в этой строке у вас не корректное значение.
Скорее у вас смещение дат
Поставьте SELECT @WrongData = '01.01.2001 00:00:00';
7. Jimbo 7 04.10.19 15:38 Сейчас в теме
(5) SELECT @WrongData = '2001-01-01 00:00:00'; помогло
8. Jimbo 7 04.10.19 15:40 Сейчас в теме
(7) ну и DECLARE @WrongData varchar(19); а не 15
6. sutygin 36 04.10.19 15:08 Сейчас в теме
И да, если база со смещением дат, то к дате надо прибавлять 2000.
И проверять на некорректные значения лучше с даты к примеру '01.01.3919 00:00:00';
Оставьте свое сообщение

См. также

Полнотекстовый поиск в 1С. №2 Самое основное для разработчика

Поиск данных v8 1cv8.cf Бесплатно (free)

Полнотекстовый поиск в 1С и все что с этим связано. Часть №2: программное использование и некоторые нюансы при разработке.

02.09.2020    5212    YPermitin    5    

Ускоряем полнотекстовый поиск в динамических списках

Поиск данных v8 1cv8.cf Россия Бесплатно (free)

Сам являюсь пользователем УТ 11.4 и при обращении пользователей пользуюсь поиском, и как многие пользователи, сталкиваюсь с медленным поиском в динамических списках. В статье приведу пример кода, который поможет исключить из поиска лишние колонки, ускорит поиск и освободит кучу ресурсов у сервера.

20.07.2020    3927    PRO100_NigGaZ    11    

Полнотекстовый поиск в 1С. №1 Грабли в динамических списках

Поиск данных v8 1cv8.cf Бесплатно (free)

Полнотекстовый поиск в 1С и все что с этим связано. Часть №1: особенности работы в динамических списках.

18.07.2020    5656    YPermitin    18    

Рекурсивный поиск каталога среди каталогов.

Поиск данных Универсальные функции v8 1cv8.cf Бесплатно (free)

Внимание особо умных. Предложенная процедура актуальна для конфигураций на платформе 8.1 и ниже. В них для функции НайтиФайлы не был реализован рекурсивный поиск в подкаталогах. Это указано в справке. Задача Найти путь к вложенному каталогу. Пример Каталог AST содержит каталоги A1, S1, T1 Каталог A1 содержит каталоги 0001, 0002, 0003 каталог S1 содержит каталоги 1001, 1002, 1003 Каталог Т1 содержит каталоги 2001, 2002, 2003 На входе функции КорневойКаталог = "D:\AST" КаталогПоиска = "1002" На выходе функции НайденныйПуть = "D:\AST\A1\1002" Уточнение - имена КаталогПоиска уникальны и не повторяются. Функция НайтиФайлы не помогает - она ищет их только в текущем каталоге.

19.09.2014    8778    betepon    5    

1С 8.2. Особенности поиска, выделение элементов

Поиск данных Работа с интерфейсом v8 1cv8.cf Бесплатно (free)

По сравнению с 8.1, интерфейс 1С 8.2 в режиме управляемого приложения претерпел значительные изменения. Кроме внешнего вида, появились так же и новые "фишки". В частности, расширен механизм поиска.

24.05.2013    23494    megabax    10    

Пример алгоритма синхронизации справочников по GUID

Практика программирования Поиск данных v8 1cv8.cf Россия Бесплатно (free)

Порой при переводе систем с одной конфигурации на другую нужно часто переносить справочники из одних в другие. При этом сложно завязываться на код или наименование. Но! Можно завязаться на GUID элемента. 1С позволяет использовать один и тот же GUID в разных справочниках.

10.02.2012    18450    fixin    6    

Готовое решение для отслеживания конфликтов кадровых неявок в ЗУП и УПП

Управление персоналом (HRM) Поиск данных Тестирование и исправление Анализ учета Управление персоналом (HRM) v8 ЗУП2.5 ЗКБУ УПП1 Бесплатно (free)

В актуальной версии ЗУП 2.5 оперативный контроль кадровых неявок сводится к программной проверке : не начинается ли в эту же самую дату какая-то другая неявка? Многие пользователи считают, что этого явно недостаточно. Вашему вниманию предлагается готовое решение (в виде программного кода) для ЗУП и УПП, которое после проведения документа кадровой неявки информирует пользователя, какие конфликты кадровых неявок повлекло за собой проведение документа.

29.06.2011    15559    megatrend    8    

Восстановление битых ссылок в 1С

Практика программирования Поиск данных v8 1cv8.cf Россия Бесплатно (free)

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

19.01.2011    33887    romansun    15