SQL Server 2016 (130) не может преобразовать дату и время «18-04-14 04:17:48.000000000 PM»

Я новичок в SQL Server 2016 (версия совместимости 130). Получение ошибки при преобразовании одного столбца varchar в datetime2. Простая попытка

select convert( datetime2, '18-04-14 04:17:48.000000000 PM', 131)

В качестве обходного пути я даже пробовал

select convert(datetime2, SUBSTRING('18-04-14 04:17:48.000000000 PM',1,21)
      + SUBSTRING('18-04-14 04:17:48.000000000 PM',29,2) , 131)

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


sql-server tsql sql-server-2016-express
person Mahendra Shinde    schedule 06.04.2018    source источник
comment
У вас есть двухзначный год... сделайте его четырехзначным и отбросьте 131 в конце. select convert( datetime2, '2018-04-14 04:17:48.000000000 PM') или используйте правильный, например select convert( datetime2, '18-04-14 04:17:48.000000000 PM',11)   -  person scsimon    schedule 06.04.2018
comment
Если можете, найдите человека, который использовал этот формат, и шлепните его. Два века века, двенадцатичасовые часы и абсурдное количество лишних цифр для долей секунды? Это... не интересно разбирать. Всегда есть время обратиться в церковь ISO 8601.   -  person Jeroen Mostert    schedule 06.04.2018

Ответы (3)


arrow_upward
3
arrow_downward

Если вы не знаете конкретную культуру [стиль], вы можете использовать простой трюк, чтобы проверить это (TRY_CONVERT):

WITH cte AS (
  select r, '18-04-14 04:17:48.000000000 PM' AS input,
     try_convert( datetime2, '18-04-14 04:17:48.000000000 PM', r) AS result
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS r 
        FROM dbo.syscolumns s1,dbo.syscolumns s2) s
  WHERE r <= 255 
)
SELECT *
FROM cte
WHERE result IS NOT NULL
ORDER BY r ASC;

демонстрация DBFiddle

Затем используйте правильный r:

SELECT TRY_CONVERT(DATETIME2, '18-04-14 04:17:48.000000000 PM', 3 ) --or 2
person Lukasz Szozda    schedule 06.04.2018
comment
Ха ... ,3 Пришлось удалить свой, когда я увидел это +1 - person John Cappelletti; 06.04.2018
comment
Интересно. Я ожидал, что время будет сброшено или что это просто не сработает, учитывая, что в документе говорится, что 3 равно dd/mm/yy. - person Larnu; 06.04.2018

arrow_upward
1
arrow_downward

Похоже, что вы использовали формат даты «итальянский», поэтому следующий простой код даст вам ответ.

выберите конвертировать (datetime2, '18-04-14 04:17:48.000000000 PM', 5)

person Ananda Kumar Jayaraman    schedule 06.04.2018

arrow_upward
0
arrow_downward

Это потому, что тег стиля, который вы предоставляете, и дата не совпадают. Тег стиля 131 в соответствии с CAST и CONVERT (Transact-SQL) использует формат dd/mm/yyyy hh:mi:ss:mmmAM. Твое свидание не похоже на это.

Ни один из тегов стиля на этой странице не соответствует стилю, который вы используете, поэтому простой CONVERT не сработает.

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

SELECT CONVERT(datetime2,REPLACE(CONVERT(varchar(10),CONVERT(datetime2,REPLACE(LEFT('18-04-14 04:17:48.000000000 PM',8),'-','/'),11),102),'.','-') + 'T' +
                         CONVERT(varchar(21),CONVERT(time,RIGHT('18-04-14 04:17:48.000000000 PM',21))))

Изменить: И несколько дополнительных примеров, и использовать против таблицы:

CREATE TABLE #Sample (OddDate varchar(50));
INSERT INTO #Sample
VALUES ('18-04-14 04:17:48.000000000 PM'),
       ('18-03-31 12:58:45.000000000 AM'),
       ('18-02-24 12:58:45.000000000 PM'),
       ('17-01-04 07:59:59.000000000 AM');

SELECT CONVERT(datetime2,REPLACE(CONVERT(varchar(10),CONVERT(datetime2,REPLACE(LEFT(S.OddDate,8),'-','/'),11),102),'.','-') + 'T' +
                         CONVERT(varchar(21),CONVERT(time,RIGHT(S.OddDate,21))))
FROM #Sample S;
GO

DROP TABLE #Sample;

Возвращает:

---------------------------
2018-04-14 16:17:48.0000000
2018-03-31 00:58:45.0000000
2018-02-24 12:58:45.0000000
2017-01-04 07:59:59.0000000
person Larnu    schedule 06.04.2018