.Net datetime with milliseconds sql server using bulkcopy thows exception

.net c# sql sqlbulkcopy sql-server-2005


I have some code that raises an exception.

SqlConnection con;
con = new SqlConnection(connectionStr);
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;     
cmd.CommandText = @"select [ID], [VERSION_ID], '08/26/2015 09:33:24:717 AM' as [Added_Dt],     [Loc_ID] as Column1
from dbo.Source where Added_Dt between CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM') and CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM')";

    cmd.CommandTimeout = con.ConnectionTimeout;
    SqlDataReader rdr = cmd.ExecuteReader();
    System.Data.SqlClient.SqlBulkCopy bcp = new SqlBulkCopy(destconnectionStr, SqlBulkCopyOptions.UseInternalTransaction);
    bcp.BatchSize = (int)Global.BatchWriteThreshold;
    bcp.DestinationTableName = destinationTableName;
    bcp.NotifyAfter = (int)Global.BatchWriteThreshold;
    bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);

destination table(SQL Server 2005): ID (int, not null), VERSION_ID (int, not null),Added_Dt(DATETIME,not null),Column1(varchar2(50) ,not null)

When used with SqlBulkCopy, this throws an error, however when used with ado.net in single updates or straight from SQL Management Studio, it works without any problems. I discovered that by removing the milliseconds, the bulkcopy executed well, but I like to have the milliseconds there. How can the problem be solved?

8/26/2015 3:30:13 PM

Accepted Answer

'08/24/2015 09:25:43:283 AM' is a date string that is invalid. the last: ought to be a. . Denotation

'08/24/2015 09:25:43.283 AM'

Of course, this converts flawlessly in SSMS:

SELECT CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM');

You must provide the precise exception message if correcting that doesn't work.

8/26/2015 3:32:32 PM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow