Convert DateTime with Decimal Time Longer Than 3 digits?

Question

I'm trying to import some data from CSV into MS SQL 2008 Server. I'm using PowerShell's Invoke-SQL, and an SQL script that utilizes BULK INSERT.

CSV fields get imported as VarChars and I'm trying to specify the datatypes.

In the data provided in the CSV (which I can't control), some of the datetime fields have a date/time in this format:

2012-03-15 15:10:08.920000000

Usually I'd just use ALTER, and let SQL convert it, and usually that works... e.g.:

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

but, when it hits one of the DateTimes like above, it fails with the error message:

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

Next, I tried SQL's Convert:

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

But I get the same error message as when using ALTER.

If I cut out the extra decimal places (leaving three or less), conversion works as expected, eg:

2012-03-15 15:10:08.920

I can't just truncate the last X characters from the field before converting, because most of the other datetime's in the column are a more traditional format like 2010-01-05 00:00:00.

While I want to keep it, the time portion is really not that important, definitely not anything after the decimal. So if the solution for converting it requires truncating it at the decimal, that's fine. :)

I could always modify the CSV before importing it into SQL via PowerShell or alike, but I'd prefer to do all my processing of the data within SQL, if possible.

So, is there a way to convert this problematic datetime format by way of an SQL query?

Answer 1

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.