Inserting and updating DATE/DATETIME database data

Data type columns are DATE or DATETIME. If one of these is in a database table, then you must set the appropriate Schema Data Type in the Database Schema Configurator dialog box. After this, the Database Outbound protocol can correctly insert/update data on a date type column.

For example, database tableA has a DATE type column columnA and the incoming message format for this column is MM/DD/YY. This means you must select MM/DD/YY as the Schema Data Type.

Note: For SQLite databases, you must use the SQLite function datetime() in the SQL statement/stored procedure to insert/update a DATETIME column.
  1. For a SQLite database, if a table column’s Database Data Type is DATETIME, select String as the Schema Data Type. To do this, click in the Schema Data Type column to open a list of choices.
  2. Use the SQLite function datetime('YYYY-MM-DD HH:MM:SS.SSS','localtime') in the SQL statement to update the date type column.
  3. Insert this into tableA(ID, columnA) values(<tableA.ID>, datetime(tableA.columnA,'localtime')), where tableA.columnA is a String Schema Data Type with format YYYY-MM-DD HH:MM:SS.SSS.