Bulk Insert Data Type Mismatch in datetime

In My Bulk Insert Query i have one of the column in .LST file as |12083121022612| Format of this column is as "YYMMDDHHMMSSTT" In dbo.ReportMain Table i have set Data Type for this as datetime It gives me error saying that

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 13 (DateAndTime).

Bulk Insert Code:-

BULK
INSERT dbo.ReportMain
FROM 'C:\AGS_WINCORE_120901.LST'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0A'
)
GO

Answers


Well, 12083121022612 is not a valid date:

select cast('12083121022612' as datetime)
-->
Conversion failed when converting date and/or time from character string.

You could bulk load into a staging table that uses a varchar field. You can convert the varchar field to the universal yyyy-MM-ddTHH:mm:ss:tt date format. Here's an example query that could insert rows from the staging table into the main table:

insert  ReportMain
        (col1)
select  cast('20' + substring(col1,1,2) + '-' + substring(col1,3,2) + '-' + 
            substring(col1,5,2) + 'T' + substring(col1, 7,2) + ':' + 
            substring(col1, 9,2) + ':' + substring(col1, 11,2) + 
            '.' + substring(col1, 13,2) as datetime)
from    ReportMain_Staging

Example at SQL Fiddle.


Need Your Help

How to check whether a multi line string contains in a text file using c#?

c# regex web-scraping

I have a text file that contains some HTML source code. I want to check whether does the text file contain a "given script". As an example:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.