Ошибка преобразования при преобразовании даты и/или времени из строки символов. SQL-запрос

У меня есть следующий SQL-запрос:

SELECT D.name, X.displayName, x.diff, X.tacticalParameter_id 

FROM (
        select displayName, tacticalParameter_id, b.value,datediff(DAY,convert(date,b.value,101),convert(date,getdate(),101)) as diff from EDA_TENANT1.EDA_T_P_A A, eda_tenant1.EDA_T_P_A_V b
        where b.attribute_id = A.ID
        and displayName like 'Fecha %'
        and value <> '') X,  
    EDA_TENANT1.EDA_T_P C, EDA_TENANT1.EDA_TACT_P_G D
    WHERE X.tacticalParameter_id = C.id
    AND C.tacticalParameterGroup_id = D.id
    and D.NAME in ('TPG Codigo postal - SPG Codigo postal',
    'TPG Cuenta bancaria - SPG Cuenta bancaria',
    'TPG Direccion - SPG Direccion',
    'TPG Doc identidad - SPG Doc identidad',
    'TPG Email - SPG Email',
    'TPG Nombre - SPG Nombre',
    'TPG Nombre empresa - SPG Nombre empresa',
    'TPG Telf empresa - SPG Telf empresa',
    'TPG Telf empresa movil - SPG Telf empresa movil',
    'TPG Telf fijo - SPG Telf fijo',
    'TPG Telf movil - SPG Telf movil',
    'TPG Codigo agente - SPG Codigo agente',
    'TPG Fiscal num - SPG Fiscal num')

который дает следующие результаты:

name    displayName diff    tacticalParameter_id
TPG Email - SPG Email   Fecha purga email   -245    48e221cc-6628-4e6b-880f-d68d0f074d72
TPG Email - SPG Email   Fecha purga email   -294    78455c15-4f60-433e-a687-121a4098baeb
TPG Nombre - SPG Nombre Fecha purga nombre y apellidos  -287    52227b6b-7497-4533-93c1-72c736175ef8
TPG Telf movil - SPG Telf movil Fecha purga telefono 2  -308    e8c465f9-cf74-4fe8-89ee-41d618055ab7
TPG Fiscal num - SPG Fiscal num Fecha purga doc identidad   -189    ed41d6a1-1c77-48d7-bfb7-eafac767084b
TPG Telf movil - SPG Telf movil Fecha purga telefono 2  -266    e7d500f2-1971-41f2-a24c-00edc7b2c7f0
TPG Email - SPG Email   Fecha purga email   -161    81593640-f080-4d11-afae-c822c4a51f05
TPG Email - SPG Email   Fecha purga email   -77 fcb880f8-cb3d-4180-9c7c-01d7cdb04022

Когда я пытаюсь поместить все результаты в новую таблицу, я получаю следующую ошибку:

SELECT D.name, X.displayName, x.diff, X.tacticalParameter_id 

  into kk_aux   -- only change

FROM (
        select displayName, tacticalParameter_id, b.value,datediff(DAY,convert(date,b.value,101),convert(date,getdate(),101)) as diff from EDA_TENANT1.EDA_T_P_A A, eda_tenant1.EDA_T_P_A_V b
        where b.attribute_id = A.ID
        and displayName like 'Fecha %'
        and value <> '') X,  
    EDA_TENANT1.EDA_T_P C, EDA_TENANT1.EDA_TACT_P_G D
    WHERE X.tacticalParameter_id = C.id
    AND C.tacticalParameterGroup_id = D.id
    and D.NAME in ('TPG Codigo postal - SPG Codigo postal',
    'TPG Cuenta bancaria - SPG Cuenta bancaria',
    'TPG Direccion - SPG Direccion',
    'TPG Doc identidad - SPG Doc identidad',
    'TPG Email - SPG Email',
    'TPG Nombre - SPG Nombre',
    'TPG Nombre empresa - SPG Nombre empresa',
    'TPG Telf empresa - SPG Telf empresa',
    'TPG Telf empresa movil - SPG Telf empresa movil',
    'TPG Telf fijo - SPG Telf fijo',
    'TPG Telf movil - SPG Telf movil',
    'TPG Codigo agente - SPG Codigo agente',
    'TPG Fiscal num - SPG Fiscal num')

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Что может быть причиной? Спасибо!

Причина точно такая же, как указано... у вас есть строка, которую нельзя преобразовать в дату и время, используя формат по умолчанию. Это одна из причин, по которой вы всегда должны использовать правильный тип данных для хранения ваших данных. SQL Server иногда должен оценивать больше строк, чем окончательный набор результатов, поэтому вы можете получить ошибку, даже если она не отображается в окончательных результатах.

Dale K 17.05.2022 22:01

Используйте try_convert() . Это вернет NULL, если преобразование завершится ошибкой. Чтобы идентифицировать проблемные строки, выберите * из вашей таблицы, где try_convert (дата, SomeCol) имеет значение null

John Cappelletti 17.05.2022 22:03

Я получаю ошибку только при попытке записать данные в новую таблицу

Vladimir Bodurov 17.05.2022 22:03
...) X, EDA_TENANT1.EDA_T_P C, EDA_TENANT1.EDA_TACT_P_G D ... Развивайтесь. Никто не должен использовать присоединяется к старому стилю
SMor 17.05.2022 22:07

...или бессмысленные псевдонимы лайк A, b, C, D.

Aaron Bertrand 17.05.2022 22:11

Ваше второе утверждение не включает утверждение INSERT? И, как я сказал выше, «иногда SQL Server должен оценивать больше строк, чем окончательный набор результатов, поэтому вы можете получить ошибку, даже если она не отображается в окончательных результатах».

Dale K 17.05.2022 22:13

@DaleK есть into kk_aux, но вам, возможно, придется прокрутить страницу, чтобы увидеть ее, в зависимости от вашего макета.

Aaron Bertrand 17.05.2022 22:29

@DaleK хорошо, в обоих запросах есть это предложение INTO. / пожимает плечами

Aaron Bertrand 17.05.2022 23:10

@AaronBertrand, так они и делают :)

Dale K 17.05.2022 23:12
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
0
9
44
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вот в чем проблема: если вы пытаетесь преобразовать строки в дату, а некоторые строки в таблице не можешь преобразовать в дату, SQL Server все равно может попытаться преобразовать их в даже если у вас есть фильтры, которые должны их устранить. Это связано с тем, что SQL Server может свободно выполнять преобразование перед фильтром или фильтр перед преобразованием в зависимости от выбранного плана (что объясняет, почему один только выбор может вести себя иначе, чем выбор в), и у вас очень мало контроля над этим. Что вы можете сделать, это изменить:

datediff(DAY,convert(date,b.value,101),convert(date,getdate(),101))

К:

CASE WHEN TRY_CONVERT(date, b.value, 101) IS NOT NULL
  THEN DATEDIFF(DAY, TRY_CONVERT(date, b.value, 101),
    CONVERT(date,GETDATE())) END 
           

Вам не нужен номер стиля при преобразовании GETDATE(), потому что он не хранится как какая-то региональная неоднозначная строка, такая как m/d/y. На самом деле вам вообще не нужно его конвертировать; независимо от того, сколько сейчас времени, DATEDIFF даст тот же результат, если вы урежете время или нет, поэтому:

CASE WHEN TRY_CONVERT(date, b.value, 101) IS NOT NULL
  THEN DATEDIFF(DAY, TRY_CONVERT(date, b.value, 101),
    GETDATE()) END 

Возможно, вы даже сможете упростить и полностью избавиться от выражения CASE, поскольку вывод не имеет большого значения, если все эти строки в конце будут отфильтрованы; это все равно даст NULL для плохих строк:

DATEDIFF
(
    DAY, 
    TRY_CONVERT(date, b.value, 101),
    GETDATE()
)

Другие вопросы по теме

Похожие вопросы

Является ли более эффективным объединение в LIKE или IN SPLIT_STRING() для строки значений, разделенных запятыми, в SQL Server?
Как сделать оператор select вставки в sql с изменяющимся значением в столбце для каждой строки
Что такое «выделенный DGA (под PGA)» и «максимальный выделенный DGA» в представлении V$PGASTAT?
Sql(dr) отправляет недостающий результат
Исключите перекрывающиеся события (начало/окончание) с учетом места проведения_id
Повторяющиеся записи Snowflake/SQL на основе значений в списке, разделенном запятыми
Как получить значения двух столбцов внешнего ключа, если они соответствуют одному и тому же ключу?
Можно ли изменить значение столбца, если он имеет более одного результата (используя GROUP BY)?
Создать вычисляемый столбец во временной таблице sql
У меня странная проблема с IDENTITY_INSERT, и я в растерянности. Может ли кто-нибудь увидеть, что мне не хватает?