Поиск некорректных дат средствами 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. Филин 117 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 35 04.10.19 09:34 Сейчас в теме
Ну задачи колонки проверить небыло. К тому же явно выгрузка валится не из за реквизитов документов, а из за выгрузки движений регистра.
4. Fox-trot 113 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 35 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 35 04.10.19 15:08 Сейчас в теме
И да, если база со смещением дат, то к дате надо прибавлять 2000.
И проверять на некорректные значения лучше с даты к примеру '01.01.3919 00:00:00';
Оставьте свое сообщение

См. также

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

Чистка базы Поиск данных Тестирование и исправление v8 1cv8.cf Бесплатно (free)

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

03.08.2007    72015    0    tormozit    227    

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

Поиск данных Универсальные функции 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    8212    0    betepon    5    

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

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

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

12.09.2013    14598    0    Abadonna    12    

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

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

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

24.05.2013    22632    0    megabax    10    

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

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

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

10.02.2012    17895    0    fixin    6    

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

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

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

29.06.2011    15087    0    megatrend    8    

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

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

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

19.01.2011    32858    0    romansun    15    

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

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

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

12.01.2010    50420    0    YAN    45    

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

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

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

09.01.2010    13077    0    dolter    33    

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

Поиск данных Анализ учета Закрытие периода Закрытие периода Учет ТМЦ Закрытие периода Учет ТМЦ v8 УТ10 Бесплатно (free)

Позволяет найти, какие партии не списались за указанный период.

21.05.2009    12860    0    kstukov    3    

Обработка поиска контрагентов на картах Яндекса

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

Поиск ведет по контактной информации.

16.04.2009    12437    0    kwazi    11    

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

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

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

24.03.2009    31283    0    Душелов    44    

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

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

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

25.12.2008    6187    0    relax2105    3    

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

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

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

11.12.2008    14010    0    PointAnd    11    

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

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

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

05.11.2008    13829    0    ValeriVP    5    

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

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

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

15.07.2008    42132    0    tormozit    11    

Поиск и замена дублирующихся элементов справочников

Чистка базы Поиск данных Тестирование и исправление v8 1cv8.cf Бесплатно (free)

Ищет дубли справочников в любых конфигурациях на платформе 8

05.11.2007    15987    0    sikvel2012    16    

Поиск и замена дублей для Планов видов характеристик - 8.0

Чистка базы Поиск данных Тестирование и исправление v8 1cv8.cf Бесплатно (free)

Аналог типовой "ПоискИЗаменаДублирующихсяЭлементов" - для планов видов характеристик.

29.10.2007    6665    0    team    2    

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

Чистка базы Поиск данных Тестирование и исправление v8 1cv8.cf Бесплатно (free)

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

14.06.2007    19113    0    denukraine    16    

Поиск документов с отличающимся периодом движений в регистрах

Обработка документов Поиск данных Тестирование и исправление v8 1cv8.cf Бесплатно (free)

Поиск документов с отличающимся периодом движений в регистрах.

30.11.2006    9974    0    IronDemon    6