Converter DateTime com tempo decimal maior que 3 dígitos?

Question

Estou tentando importar alguns dados do CSV para o MS SQL 2008 Server. Estou usando o Invoke-SQL do PowerShell e um script SQL que utiliza BULK INSERT .

Os campos CSV são importados como VarChars e estou tentando especificar os tipos de dados.

Nos dados fornecidos no CSV (que não posso controlar), alguns dos campos de data e hora têm uma data / hora neste formato:

2012-03-15 15:10:08.920000000

Normalmente, eu usava o ALTER e deixava o SQL convertê-lo, e normalmente isso funciona ... por exemplo:

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

mas, quando atinge um dos DateTimes como acima, ele falha com a mensagem de erro:

Conversion failed when converting date and/or time from character string.

Em seguida, eu tentei o Convert do SQL:

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

Mas recebo a mesma mensagem de erro quando uso o ALTER.

Se eu cortar as casas decimais extras (deixando três ou menos), a conversão funciona como esperado, por exemplo:

2012-03-15 15:10:08.920

Eu não posso apenas truncar os últimos caracteres X do campo antes de converter, porque a maioria dos outros datetime da coluna é um formato mais tradicional como 2010-01-05 00:00:00 .

Enquanto eu quero mantê-lo, a parte do tempo não é tão importante, definitivamente não é nada depois do decimal. Então, se a solução para convertê-lo exigir truncá-lo no decimal, tudo bem. :)

Sempre posso modificar o CSV antes de importá-lo para o SQL por meio do PowerShell ou algo semelhante, mas prefiro fazer todo o processamento dos dados no SQL, se possível.

Existe uma maneira de converter esse formato problemático de data e hora por meio de uma consulta SQL?

    

Answer 1

DATETIME2 é o tipo de dados SQL que você está procurando. Leva precisão de segundos a 7 casas decimais. O TechNet fornece informações completas, com exemplos comparando os vários tipos aqui .

As informações a seguir fornecem informações básicas sobre todos os tipos aplicáveis:

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

Produz os resultados:

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

Acima da consulta e do resultado obtidos diretamente da página da TechNet citada acima.

N.B. Acredito que DATETIME2 apareceu pela primeira vez no SQL 2008.