Блог

Я использую SQL Server 2017. Я пытаюсь обработать строки из поля свободного формата и либо преобразовать их в дату в формате «дд/мм/гггг», либо, если они не в этом формате, просто отобразите текст дословно.

Мне это нужно в ПРЕДСТАВЛЕНИИ, поэтому я не могу использовать ЗАДАННЫЙ ЯЗЫК. Звучит просто при использовании Convert и IsDate , кажется, не работает.

Поэтому для приведенного ниже фрагмента кода (помните, что это будет в представлении) я хочу прочитать текст, и если строка преобразуется в дату (т. Е. находится в формате dd/mm/yyyy , затем запустите преобразование в дату, как мне нужно, в формате даты, чтобы Excel мог выбрать (через подключение базы данных SQL Server)), и если оно не преобразуется в дату, отобразите текст как есть.

 create table dateTest1 
( 
    idx int, 
    dateStringTest varchar(15) 
); 
  
insert into dateTest1 (idx, dateStringTest) 
values (1, '13/01/2021'), (2, 'no'); 
 
select 
    case 
        when isdate(convert(datetime, dateStringTest, 103)) = 1 
            then convert(datetime, dateStringTest, 103)
        else dateStringTest 
    end as dtres
from 
    dateTest1 
--where idx = 1 
 

Ошибка:

Msg 241, Уровень 16, Состояние 1, Строка 15
Сбой преобразования при преобразовании даты и/или времени из символьной строки.

Эта ошибка возникает при idx = 2. Idx = 1 работает нормально.

Любая помощь в этом будет весьма признательна. Заранее спасибо.

Вам нужно привести полученную дату к a varchar . A case expression может возвращать только один тип данных, а порядок приоритета означает, что он все еще пытается преобразовать значения varchar в datetime

 select 
       case when isdate( dateStringTest) = 1 
       then Cast(convert(datetime, dateStringTest, 103) as varchar(10))
       else dateStringTest 
       end as dtres
from dateTest1 
 

Вы можете сжать в один оператор (применяется тот же порядок приоритета)

 select IsNull(Cast(Convert(datetime,Try_Cast(dateStringTest as date),103) as varchar(10)),dateStringTest)
from datetest1
 

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

Единственный способ сделать это в одной колонке-оставить ее в виде текста.

Ваше использование ISDATE() неверно. Если dateStringTest не является допустимой датой, CONVERT() на SQL Server возникнет ошибка типа:

Преобразование типа данных varchar в тип данных datetime привело к значению вне диапазона.

Вот почему вы получаете ошибку для idx=2

Вы говорите, что вам «нужно это в формате даты, чтобы Excel мог подобрать». То, как Excel интерпретирует это, вероятно, будет зависеть от ваших языковых настроек. Предполагая mm/dd/yyyy , что это допустимо в вашем регионе, Excel, вероятно, уже рассматривает это как дату. Но я предполагаю, что этого не происходит, поэтому mm/dd/yyyy это недопустимо для вашего региона. Для меня, если у меня есть эта таблица в SQL…

 create table dateTest1 (
    id int identity(1,1) not null,
    dateStringTest varchar(20)
)

insert dateTest1
values
  ('2020-01-01')
, ('2020-21-01')
, ('2020-01-21')
, ('1/1/2020')
, ('1/21/2020')
, ('21/1/2020')
, ('21/01/2020')
, ('other stuff')
 

…и запросите его из Excel, добавив столбцы с функциями DATEVALUE , DAY , WEEKDAY , и YEAR , я получу…

Это, по-видимому, указывает на то, что Excel распознает некоторые значения как даты.

Что вам нужно сделать , так это попытаться преобразовать значение в datetime , а затем, если это не удастся, сообщить об исходном значении. В обоих случаях выведите строку.

Вам следует ознакомиться с документацией для CONVERT() .

Попробуйте это:

 select dateStringTest
, coalesce(cast(try_convert(datetime, dateStringTest, 103) as varchar(20)), dateStringTest) as dtres1
, coalesce(convert(varchar(20), try_convert(datetime, dateStringTest, 103), 103), dateStringTest) as dtres2
, coalesce(convert(varchar(20), try_convert(datetime, dateStringTest, 103), 101), dateStringTest) as dtres3
from dateTest1
 

Обновить

Если вы рассматриваете dd/mm/yyyy в качестве дат только значения, соответствующие шаблону (поэтому 17/01/2021 является датой, а 17/1/2021-нет), этот метод грубой силы будет работать с SQL Server 2008 (уровень совместимости 100):

(Обратите внимание, что я также обновил свой ввод выше.)

 ;
with a as (
    select id
    , dateStringTest
    , SUBSTRING(dateStringTest, 7, 4)   '-'   SUBSTRING(dateStringTest, 4, 2)   '-'   SUBSTRING(dateStringTest, 1, 2) as converteDate
    from dateTest1
)

select id
, cast(convert(datetime, dateStringTest, 103) as varchar(20)) as dtres
from a
where isdate(converteDate) = 1

union
select id
, dateStringTest as dtres
from a
where isdate(converteDate) = 0