Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Внезапный Conversion failed when converting date and/or time from character string.  [new]
ectacoh
Member

Откуда:
Сообщений: 4
Здравствуйте, уважаемые коллеги.
Помогите, пожалуйста, понять, отчего у меня возникает ошибка, я сломал уже всю голову, но сам разобраться не могу.

Дано:
Таблица tbl_LogItemsTypes в ней столбец Message. Сообщения бывают разные, но меня интересуют только сообщения вида "Request timestamp: Fri, 12 Jun 2015 09:49:05 GMT expired. It must be within 300 secs/ of server time.", время различное.

Задача: найти все начала блоков последовательных дат. То есть, если сегодняшняя дата есть, а вчерашней нету - подходит, если вчерашняя тоже есть - отбрасываем.

Итерационно двигался так:

Шаг 1: простая выборка интересующих сообщений
SELECT     Message
FROM         tbl_LogItemsTypes
WHERE     (Message LIKE '% It must be within 300 secs/ of server time.')


работает

Шаг 2: вычленение собственно даты, дистинкт, сортировка
SELECT DISTINCT CAST(SUBSTRING(Message, 25, 11) AS datetime) AS dt
FROM         tbl_LogItemsTypes
WHERE     (Message LIKE '% It must be within 300 secs/ of server time.')
ORDER BY dt


работает, выдаёт результат вида
2015-06-20 00:00:00.000
2015-06-21 00:00:00.000
2015-10-09 00:00:00.000
2015-10-10 00:00:00.000


Шаг 3: формирую список "завтр"

SELECT     DATEADD(day, 1, CAST(SUBSTRING(Message, 25, 11) AS datetime)) AS dt
FROM         tbl_LogItemsTypes
WHERE     (Message LIKE '% It must be within 300 secs/ of server time.')


работает, выдаёт результат вида
2015-06-21 00:00:00.000
2015-06-22 00:00:00.000
2015-06-22 00:00:00.000
2015-10-11 00:00:00.000
2015-10-10 00:00:00.000


Шаг 4: Теперь получается, что если дата из шага 2 отсутствует в выборке шага 3, то у этой даты "нет вчера" (так как "завтра" для "вчера" это как раз та самая дата). Решаю фильтровать через NOT IN, но в начале пишу просто с IN для проверки синтаксиса

SELECT DISTINCT dt FROM
(SELECT CAST(SUBSTRING(Message, 25, 11) AS datetime) AS dt
	FROM          tbl_LogItemsTypes
	WHERE      Message LIKE '% It must be within 300 secs/ of server time.') q
WHERE dt IN(
SELECT DATEADD(day, 1, CAST(SUBSTRING(Message, 25, 11) AS datetime))
	FROM          tbl_LogItemsTypes 
	WHERE      Message LIKE '% It must be within 300 secs/ of server time.')
ORDER BY dt


работает, выдаёт результат вида
2015-06-21 00:00:00.000
2015-10-10 00:00:00.000

то есть только те даты, у которых есть "вчера.

Шаг 5: добавляю NOT

SELECT DISTINCT dt FROM
(SELECT CAST(SUBSTRING(Message, 25, 11) AS datetime) AS dt
	FROM          tbl_LogItemsTypes
	WHERE      Message LIKE '% It must be within 300 secs/ of server time.') q
WHERE dt NOT IN(
SELECT DATEADD(day, 1, CAST(SUBSTRING(Message, 25, 11) AS datetime))
	FROM          tbl_LogItemsTypes 
	WHERE      Message LIKE '% It must be within 300 secs/ of server time.')
ORDER BY dt


Результат:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


Причины ошибки мне не понятны. Я полагаю, каким-то образом, перестаёт срабатывать

	WHERE      Message LIKE '% It must be within 300 secs/ of server time.')


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

Bonus track:
Переписывание запроса через EXCEPT, вот таким образом:

SELECT CAST(SUBSTRING(Message, 25, 11) AS datetime) AS dt
	FROM          tbl_LogItemsTypes
	WHERE      Message LIKE '% It must be within 300 secs/ of server time.'
EXCEPT
SELECT DATEADD(day, 1, CAST(SUBSTRING(Message, 25, 11) AS datetime))
	FROM          tbl_LogItemsTypes 
	WHERE      Message LIKE '% It must be within 300 secs/ of server time.' 

даёт ту же ошибку.

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

С уважением, Станислав.
21 май 17, 10:47    [20499188]     Ответить | Цитировать Сообщить модератору
 Re: Внезапный Conversion failed when converting date and/or time from character string.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
План смотрите. Сервер не обязан сначала делать фильтр LIKE, а потом соединять наборы.
21 май 17, 10:50    [20499193]     Ответить | Цитировать Сообщить модератору
 Re: Внезапный Conversion failed when converting date and/or time from character string.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30763
ectacoh
Причины ошибки мне не понятны. Я полагаю, каким-то образом, перестаёт срабатывать

	WHERE      Message LIKE '% It must be within 300 secs/ of server time.')



и в выборку начинают попадать не те строки, которые невозможно распарсить.
Да, именно так. Серверу удобнее вычислить для всех записей CAST(SUBSTRING(Message, 25, 11) AS datetime), а потом уже применить к ним WHERE
В таких случаях нужно всегда пользоваться CASE
CASE Message LIKE '% It must be within 300 secs/ of server time.' THEN CAST(SUBSTRING(Message, 25, 11) AS datetime) ELSE NULL END
21 май 17, 21:13    [20499761]     Ответить | Цитировать Сообщить модератору
 Re: Внезапный Conversion failed when converting date and/or time from character string.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7397
Начиная с 2012 можно использовать trycast(), tryconvert().
22 май 17, 12:31    [20501000]     Ответить | Цитировать Сообщить модератору
 Re: Внезапный Conversion failed when converting date and/or time from character string.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30763
Владислав Колосов
Начиная с 2012 можно использовать trycast(), tryconvert().
Но в данном лучше повторить условие where

Потому что, если ошибка в условии, то сервер проигнорирует "не ту" строку.
22 май 17, 13:22    [20501160]     Ответить | Цитировать Сообщить модератору
 Re: Внезапный Conversion failed when converting date and/or time from character string.  [new]
ectacoh
Member

Откуда:
Сообщений: 4
Гавриленко Сергей Алексеевич,
И впрямь, по плану сперва Compute Scalar, а потом Filter.
Спасибо за ответ.
23 май 17, 04:27    [20502987]     Ответить | Цитировать Сообщить модератору
 Re: Внезапный Conversion failed when converting date and/or time from character string.  [new]
ectacoh
Member

Откуда:
Сообщений: 4
alexeyvg,

Да, спасибо, попробовал переписать через CASE, сработало отлично.
23 май 17, 04:28    [20502988]     Ответить | Цитировать Сообщить модератору
 Re: Внезапный Conversion failed when converting date and/or time from character string.  [new]
ectacoh
Member

Откуда:
Сообщений: 4
Владислав Колосов,

Спасибо, попробую.
23 май 17, 04:31    [20502990]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить