Блог

Главная - Вопросы по программированию - Исправить строку даты и времени в SQL Server

Исправить строку даты и времени в SQL Server

#sql-server #tsql #datetime #iso8601

#sql-сервер #tsql #дата и время #iso8601

Вопрос:

У меня есть таблица с 43 000 000 строк. В столбце datetime данные выглядят 2020.04.22T04:39:29.359 и имеют тип VARCHAR .

Это похоже на формат ISO, но они есть . , и для ISO мне нужно - . Каков наилучший способ преобразовать значения в этих строках в datetime?

Одним из моих вариантов был subst . to -:

 UPDATE table 
SET [Column 3] = REPLACE([Column 3], '.', '-');

но тогда мне нужно отрезать микросекунды от конца.

Как сделать этот разрез?

Или, может быть, вы можете посоветовать более верный способ.

Комментарии:

1. «и это тип VARCHAR » . Реальный вопрос в том, почему вы вообще используете a varchar ? Исправьте дизайн, устраните проблему.

2. Имейте в виду, что DATETIME , в отличие DATETIME2 от , не поддерживает полную точность в миллисекундах, это только с точностью (если это можно так назвать) до 3 миллисекунд. Значение , подобное 04:39:29.359 , в частности , не может быть точно представлено и будет округлено до 04:39:29.360 . Для некоторых приложений это не имеет значения, для других это имеет значение (когда данные должны сравниваться с оригиналом). Так что, хотя VARCHAR это определенно не тот тип, который нужно использовать, DATETIME это тоже не обязательно.

Ответ №1:

Вы можете использовать TRY_CONVERT здесь, предварительно немного помассировав, чтобы привести ваши необработанные строки datetime в формат, который распознает SQL Server:

 UPDATE yourTable
SET new_dt_col = TRY_CONVERT(
                     datetime,
                     REPLACE(LEFT(dt_col, 19), '.', '-')   '.'   RIGHT(dt_col, 3)
                 );

Чтобы быть явным, логика замены, используемая выше, сначала преобразует это:

 2020.04.22T04:39:29.359

в это:

 2020-04-22T04:39:29.359

Вы можете сами убедиться, что следующее преобразование работает правильно:

 SELECT TRY_CONVERT(datetime, '2020-04-22T04:39:29.359');

Ответ №2:

Если каждая отдельная строка в таблице имеет дату, которая соответствует формату 2020.04.22T04:39:29.359, вы можете выполнить следующую инструкцию update:

 UPDATE table 

SET [Column 3] = SUBSTRING([Column 3],1,4)   '-'   SUBSTRING([Column 3],6,2)   '-'   SUBSTRING([Column 3],9,15)

Исправить только 5-й и 8-й символы, не затрагивая символ «.» из микросекунд.
После этого вы сможете выполнить преобразование в datetime.

Метки: