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
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:
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:
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
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?