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

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

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

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

2

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

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

См. также

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

Статья Программист Нет файла v8 1cv8.cf Windows Бесплатно (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    6832    betepon    5       

Галка "Поиск по подстроке" сразу 12

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

Клиенты любят посылать обрезанные номера проблемных документов, например, в таком виде: документ 4874 от 12.09.2013, хотя на самом деле он 00000004874. Надоело мне постоянно ставить в окне поиска галку "Поиск по подстроке"!

12.09.2013    13037    24    Abadonna    12       

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

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

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

24.05.2013    20792    megabax    10       

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

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

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

10.02.2012    16433    fixin    6       

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

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

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

29.06.2011    13511    megatrend    8       

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

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

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

19.01.2011    30843    romansun    15       

Поиск ссылок на объект для последующего удаления объектов. 271

Инструменты и обработки Системный администратор Внешняя обработка (ert,epf) v8 1cv8.cf Россия Бесплатно (free) Чистка базы Поиск данных

Иногда бывает нужно найти все ссылки на конкретный документ или любой другой объект системы, чтобы все это удалить...

12.01.2010    47885    2623    YAN    44       

"Глобальный" поиск (легкая замена полнотекстовому) 14

Инструменты и обработки no Внешняя обработка (ert,epf) v8 1cv8.cf Россия Бесплатно (free) Поиск данных

Есть такая штука в Axapta, как "Глобальный поиск"... Правда я ее увидел после того, как сделал первый вариант своего, но... потом пришлось дорабатывать по аксаптовский, уж больно он мне понравился :-) Но покажу только первый вариант.

09.01.2010    11914    206    dolter    33       

[IFilter] Поиск в содержании файлов из 1С (ВК, .NET) 14

Инструменты и обработки Программист Компонента, плагин (dll, vbs,..) v7.7 v8 1cv8.cf 1cv7.md Windows Бесплатно (free) Поиск данных Разработка внешних компонент

Компонента для работы с фильтрами поисковых систем. Получает текст из файла по установленном в системе фильтрам. Индексирует и ищет файлы по их содержанию (аналог Desktop Search) Поддерживает *.doc, *.xls, *.txt, *.ppt. Дополнительные фильтры можно скачать с http://www.ifilter.org/Links.htm

24.03.2009    28642    141    Душелов    44       

Просмоторщик 3

Инструменты и обработки no Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Обработка документов Поиск данных Работа с интерфейсом

Более удобный доступ к документам информационной базы и поиск среди них по реквизиту документа или табличной части

25.12.2008    5323    104    relax2105    3       

Поиск подчиненных документов 12

Инструменты и обработки no Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Обработка документов Поиск данных Универсальные функции

Обработка ищет подчиненные документы. Т.к. эта обработка разрабатывалась под конкретную задачу (нет ничего лишнего), некоторые могут не увидеть в ней некоторых возможностей.

11.12.2008    12811    427    PointAnd    11       

Обработка для замены ссылок 23

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Поиск данных Инструментарий разработчика Тестирование и исправление

Обработка предназначена для замены ссылок. Отличия от аналогов - возможность выполнения заданного пользователем алгоритма для заполнения таблицы замен.

05.11.2008    12661    400    ValeriVP    5       

Проверка регистров сведений перед усечением типов измерений 41

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Сервисные утилиты Поиск данных Тестирование и исправление

Если при усечении типов измерений регистров сведений нарушается уникальность записей, то конфигуратор не дает применить конфигурацию к БД с ошибкой "Записи регистра сведений ... стали не уникальными!" до 8.2.14 и "Имеются записи с одинаковыми измерениями" начиная с 8.2.14. Данная обработка позволит быстро найти такие записи. Обработка больше не поддерживается в мобильном варианте. Теперь она включена в подсистему "Инструменты разработчика" http://infostart.ru/public/15126/

15.07.2008    38782    1673    tormozit    11       

Поиск и замена дублей + v0.99 436

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Чистка базы Поиск данных Тестирование и исправление

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

03.08.2007    68314    5674    tormozit    222       

Универсальная обработка по поиску дубликатов в справочниках для 1С 8.0-8.1 38

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Чистка базы Поиск данных Тестирование и исправление

Универсальная обработка по поиску дубликатов в любых справочниках по любым реквизитам!!! Теперь и для 8 версии 1С. Была сделана в связи с неудобностью стандартной обработки, и отображения "похожих" дубликатов, т.е. не 100%.

14.06.2007    17631    1013    denukraine    16