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

Question

Я пытаюсь импортировать некоторые данные из 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-запроса?

Answer 1

DATETIME2 - это тип данных SQL, который вы ищете. Требуется секундная точность до 7 знаков после запятой. TechNet предоставляет полную информацию, с примерами сравнения различных типов здесь.

Следующее даст вам основы относительно всех применимых типов:

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';

Производит результаты:

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

Вышеупомянутый запрос и результат взяты непосредственно со страницы TechNet, указанной выше.

NB Я считаю, что DATETIME2 впервые появился в SQL 2008.