I have this code, which throws 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; con.Open(); 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); bcp.WriteToServer(rdr);
destination table(SQL Server 2005): ID (int, not null), VERSION_ID (int, not null),Added_Dt(DATETIME,not null),Column1(varchar2(50) ,not null)
This throws exception if used with SqlBulkCopy but runs without any issues when using ado.net in single updates or directly from SQL Management Studio. I found that the removal of milliseconds part makes the bulkcopy run without exceptions but then I want the milliseconds to be there. How to resolve the issue?
'08/24/2015 09:25:43:283 AM' is not a valid date string. The final
: should be a
'08/24/2015 09:25:43.283 AM'
Of course, this does convert without error in SSMS:
SELECT CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM');
If fixing that doesn't help, then you need to provide the exact exception message.