conversion failed when converting date and/or time from character string in mule batch commit

Question

I am executing merge statement for inserting/updating a column of datetime data type in sql server.

payload.date looks like 2014-02-03 00:00:00.000

Batch fails with the error 'conversion failed when converting date and/or time from character string'

     <batch:step name="step_1">
     <batch:commit  doc:name="Batch Commit" size="100"> 
            <db:execute-ddl config-ref="config"  doc:name="merge" >
                <db:dynamic-query><![CDATA[
                BEGIN  
                MERGE tablename AS Target 
                USING (SELECT '#[payload.key]')  AS Source (ID) 
                ON (Destination.Id=Source.ID) 
                WHEN MATCHED THEN UPDATE SET 
                datefield = '#[payload.date]' 
                WHEN NOT MATCHED THEN
               INSERT (datefield) values ('#[payload.date]');

                END]]></db:dynamic-query>
                            </db:execute-ddl>
            </batch:commit> 
    </batch:step>

If i remove batch commit, i am not getting any error and the merge statement works fine and the dates are updated/inserted into DB. I want to use batch commit, how can i resolve this issue?

Answer 1

bulk mode is not supported for execute ddl operation so batch commit can't be used