Блог

Использование SQL Server — мой вопрос: как я могу по-прежнему получать ошибки преобразования, хотя в CTE есть только строки с правильными датами?

Если я создам CTE только со строками, имеющими действительные даты… Я все еще получаю ошибку.

 WITH goodDates AS
(
    SELECT * 
    FROM impExpRaw2 
    WHERE ISDATE(dateofservice) = 1 
      AND DateofService <> '' 
      AND DateofService IS NOT NULL
)
SELECT * 
FROM goodDates 
WHERE DATEDIFF(d, '7/31/2020', dateofservice) > 0 
  

Это приводит к следующему сообщению об ошибке, которое я ожидал бы, если бы в этом поле была дата типа ‘2/31/2020’ или ‘cat’…

Сообщение 241, уровень 16, состояние 1, строка 293
Ошибка преобразования при преобразовании даты и / или времени из символьной строки.

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

Я могу выбрать во временной таблице, а затем выполнить dateDiff, и все работает нормально. Для меня тот факт, что это работает, действительно исключает сумасшедшие данные или поля со скрытым chr (0) или что-то встроенное в него.

 select * 
into #gd 
from impExpRaw2 
where isdate(dateofservice) = 1   

select * 
from #gd 
where datediff (d, '7/31/2020', dateofservice) > 0 
  

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

Информация о версии для заинтересованных

 Microsoft SQL Server 2016 (RTM-GDR) (KB3164398) - 13.0.1708.0 (X64)   
Copyright (c) Microsoft Corporation  
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) 
  

Я бы рекомендовал, try_cast() а не isdate() : это намного надежнее, поскольку он фактически пытается принудительно привязать строку к дате, а не полагаться на какую-то сложную эвристику.

Вы могли бы сформулировать запрос как:

 select *
from impExpRaw2
where try_cast(dateofservice as datetime) >= '20200801'
  

try_cast() возвращает, null когда преобразование завершается неудачно, что не удовлетворяет where предикату. Обратите внимание, что при использовании этого метода вам не нужно явно отфильтровывать null или пустые значения (функция преобразования также вернет null значения).

Наконец: при объявлении литералов даты безопаснее использовать формат YYYYMMDD , который SQL Server всегда распознает как дату, независимо от региональных настроек.

КАК я МОГУ ПО-ПРЕЖНЕМУ ПОЛУЧАТЬ ОШИБКУ ПРЕОБРАЗОВАНИЯ, УЧИТЫВАЯ, ЧТО В CTE ЕСТЬ ТОЛЬКО СТРОКИ С ХОРОШИМИ ДАТАМИ

Потому что SQL Server оптимизирует весь запрос и фактически не запускает подзапрос CTE «первым». SQL Server можно свободно применять

 DATEDIFF(d, '7/31/2020',dateofservice)
  

перед применением любого из

 ISDATE(dateofservice)=1 
  

и то, что он выбирает, зависит от плана выполнения.