I’m using SQL and depending on the table column type (in my case datetime), I need to use the formatter rnow(‘yyyy-dd-mm’)].

Hi Russell, 

The value in the Update column select can only support static strings in the current release of CData Arc (23.2.8609.0 as of the time of this post), so you wouldn’t be able to resolve either an ArcScript expression or a SQL function directly in that field, but Arc does provide support for the ability to customize post-processing queries in your SELECT mappings.

CData Arc - Database Advanced Configuration | Version 23.2.8609

I’ve included a simple example of this here - in the SQLite table below, ID is the key column, and for each record queried, the application will update that record to use the current timestamp on the column: 

<Items>
    <Record table="`Record`">
        <ID type="int" key="true" />
        <Name />
        <PROCESSED_AT type="datetime" />
        <UpdateProcessed table="`Record`" selectQuery="UPDATE `Record` SET `PROCESSED_AT`= CURRENT_TIMESTAMP WHERE ID=${ID}" outputResult="false" />
    </Record>
</Items>

Some minor details about the attributes on that element:

The table attribute signals to the connector to treat UpdateProcessed like a new instruction, and not a column.

The selectQuery overrides the behavior of the select query to use a custom query

The ${ID} element in the query resolves to the ID column of the previous return

The outputResult attribute tells the connector not to bother outputting anything in the output file. 

Can you give this a try and let me know if you are able to use it to update the manifest shipments as you collect them? 

Another option here is to add a trigger on the SQL side to do this automatically on a select condition.