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?

  • 3
    Have you tried using DATETIME2? By default, seconds precision goes to 7 places. – BillP3rd Jan 14 '16 at 22:54
  • 2
    Have you considered using datetime2 instead of datetime? Datetime only holds 3 digits past seconds, datetime2 holds up to 7 digits. – Scott Chamberlain Jan 14 '16 at 22:54
  • Hmmm datetime2 is a new one to me. Maybe that's the best bet after all. Ok I checked it and it works in my convert example -- nice! I'm low on time right now, so I can't test it using ALTER (which is my preference for what I'm doing) right this second, but if it works (which I fully expect it will) I'll happily accept that as the answer, if someone wants to write it up officially. :) – Ƭᴇcʜιᴇ007 Jan 14 '16 at 23:04
  • 2
    PS: this was my first-ever SU question. :) – Ƭᴇcʜιᴇ007 Jan 14 '16 at 23:05

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:

     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.

| improve this answer | |
  • why this is in Superuser ? – Rafael Jan 14 '16 at 23:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.