Developer forum

Try using UtcNow instead of Now

Same thing:

2026-06-15 16:18:48.8458|DataIntegration|ERROR|Error: Failed to remove rows from Table [dbo.Staging_T_PriceDiscTable] that where not present in source. Exception message: Conversion failed when converting date and/or time from character string. Sql query: DECLARE @r INT; SET @r = 1; WHILE @r > 0 BEGIN Delete top(100000) from [dbo].[Staging_T_PriceDiscTable] where NOT EXISTS  (SELECT * FROM [dbo].[Staging_T_PriceDiscTableTempTableForBulkImport1] WHERE ([dbo].[Staging_T_PriceDiscTable].[recid]=[recid] or ([dbo].[Staging_T_PriceDiscTable].[recid] is null and [recid] is null)) )  AND ( ([module] = @conditional10 ) AND ([inventdimid] = @conditional11 Or [inventdimid] = @conditional12 ) AND ([todate] < '02-02-1900 00:00:00' Or [todate] > '15-06-2026 14:09:14' )  )  SET @r = @@ROWCOUNT; END Stack:    at Dynamicweb.DataIntegration.Integration.BaseSqlWriter.DeleteRowsFromMainTable(Boolean deleteExistingRows, Dictionary`2 mappings, String extraConditions, SqlCommand sqlCommand)    at Dynamicweb.DataIntegration.Integration.BaseSqlWriter.DeleteExcessFromMainTable(SqlCommand sqlCommand, Mapping mapping, String extraConditions, String tempTablePrefix, Boolean removeMissingAfterImportDestinationTablesOnly)    at Dynamicweb.DataIntegration.Providers.DynamicwebProvider.DynamicwebBulkInsertDestinationWriter.DeleteExcessFromMainTable(String shop, SqlTransaction transaction, Boolean deleteProductsAndGroupForSpecificLanguage, String languageId, Boolean hideDeactivatedProducts)    at Dynamicweb.DataIntegration.Providers.DynamicwebProvider.DynamicwebProvider.RunJob(Job job)

Not surprised though, as the first conditional had an explicit ToString but that is also ignored and converted to 02-02-1900 00:00:00'

It looks this is is coming from within the provider without an option to control this, right? Any clever workarounds?

Imar

Hi Imar,
could you try this instead?
@Code(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"))
The problem is that the result of the Code expression evaluation is then converted to string: expressionValue.ToString() and then the string value is used.

BR, Dmitrij

Same thing. Again, not a big surprise as the first where filter I posted already used a similar format.

Note that this is about the *delete* of excess  rows after the import. The original filter works fine; it's when removing rows that it fails. It looks like it uses the filter in a different format somehow.

Imar

Here's my setup BTW:

Hi Imar,
The root cause is a culture bug in how @Code results get serialised to a string internally. When @Code(System.DateTime.Now) is evaluated, it returns a DateTime object; we then call .ToString() on it without specifying a culture, so on the Belgian server it produces "15-06-2026 11:51:56" (DD-MM-YYYY), which SQL Server can't implicitly convert.

Workaround until we ship a fix:

Force the expression to return an ISO-formatted string rather than a DateTime, and use the full namespace for CultureInfo (the code evaluator only imports System and System.Collections.Generic):

@Code(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", System.Globalization.CultureInfo.InvariantCulture))

 For your static 1900-02-02 lower bound, use dashes (not slashes — SQL Server doesn't recognise YYYY/MM/DD): 

@Code(new System.DateTime(1900, 2, 2).ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture))

 BR, Dmitrij

Thanks Dmitrij, but it keeps failing. I have:

@Code(new System.DateTime(1900, 2, 2).ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture))
@Code(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", System.Globalization.CultureInfo.InvariantCulture))

(also tried with ToString("s") which should produyce an ISO date, but same result).

With this, I get the same error on this Where clause: AND ([todate] < '02-02-1900 00:00:00' Or [todate] > '16-06-2026 09:49:59' )  )  

It looks like the date time is reevaluated somewhere and then converted to the wrong format when used in the delete statement?

Imar