Конвертировать DateTime с десятичным временем длиннее 3 цифр?

1364
Ƭᴇcʜιᴇ007

Я пытаюсь импортировать некоторые данные из CSV в MS SQL 2008 Server. Я использую PowerShell Invoke-SQL и сценарий SQL, который использует BULK INSERT.

Поля CSV импортируются как VarChars, и я пытаюсь указать типы данных.

В данных, предоставленных в CSV (которые я не могу контролировать), некоторые поля даты и времени имеют дату / время в следующем формате:

2012-03-15 15:10:08.920000000

Обычно я просто использую ALTER и позволяю SQL преобразовывать его, и обычно это работает ... например:

ALTER TABLE [dbo].[ImportData] ALTER COLUMN [PlanSetupDate] datetime null;

но, когда он попадает в один из типов DateTime, как указано выше, происходит сбой с сообщением об ошибке:

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

Затем я попытался преобразовать SQL:

Select Convert(datetime, '2012-03-15 15:10:08.920000000')

Но я получаю то же сообщение об ошибке, что и при использовании ALTER.

Если я вырежу лишние десятичные разряды (оставляя три или меньше), преобразование будет работать как положено, например:

2012-03-15 15:10:08.920

Я не могу просто обрезать последние X символов из поля перед преобразованием, потому что большинство других дат и времени в столбце имеют более традиционный формат 2010-01-05 00:00:00.

Хотя я хочу сохранить его, доля времени на самом деле не так важна, определенно ничего после десятичного числа. Так что, если решение для его преобразования требует усечения его в десятичном формате, это нормально. :)

Я всегда мог изменить CSV, прежде чем импортировать его в SQL через PowerShell или тому подобное, но я бы предпочел всю свою обработку данных в SQL, если это возможно.

Итак, есть ли способ преобразовать этот проблемный формат даты и времени с помощью SQL-запроса?

3
Вы пытались использовать DATETIME2? По умолчанию точность секунд доходит до 7 мест. BillP3rd 8 лет назад 3
Вы рассматривали возможность использования `datetime2` вместо` datetime`? Datetime содержит только 3 цифры за секунды, datetime2 содержит до 7 цифр. Scott Chamberlain 8 лет назад 2
Хммм datetime2 новый для меня. Может быть, это лучшая ставка в конце концов. Хорошо, я проверил это, и это работает в моем примере преобразования - хорошо! Сейчас у меня мало времени, поэтому я не могу проверить это, используя ALTER (что я предпочитаю делать) прямо сейчас, но если это сработает (что я полностью ожидаю), я буду рад примите это как ответ, если кто-то хочет официально это написать. :) Ƭᴇcʜιᴇ007 8 лет назад 0
PS: это был мой первый в истории SU вопрос. :) Ƭᴇcʜιᴇ007 8 лет назад 2

1 ответ на вопрос

2
BillP3rd

DATETIME2 is the SQL data type you're looking for. It takes seconds precision to 7 decimal places. TechNet gives full information, with examples comparing the various types here.

The following will give you the basics with respect to all of the applicable types:

SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime' ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2' ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset';

Produces the results:

Data type Output time 12:35:29. 1234567 date 2007-05-08 smalldatetime 2007-05-08 12:35:00 datetime 2007-05-08 12:35:29.123 datetime2 2007-05-08 12:35:29.1234567 datetimeoffset 2007-05-08 12:35:29.1234567 +12:15

Above query and result taken directly from the TechNet page cited above.

N.B. I believe that DATETIME2 first appeared in SQL 2008.

почему это в суперпользователе? Rafael 8 лет назад 0

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