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 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
.
Note: For SQLite databases, you must
use the SQLite function
datetime()
in the SQL
statement/stored procedure to insert/update a DATETIME
column. -
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 theSchema Data Type
column to open a list of choices. -
Use the SQLite function
datetime('YYYY-MM-DD HH:MM:SS.SSS','localtime')
in the SQL statement to update the date type column. -
Insert this into
tableA(ID, columnA) values(<tableA.ID>, datetime(tableA.columnA,'localtime'))
, wheretableA.columnA
is a StringSchema Data Type
with format YYYY-MM-DD HH:MM:SS.SSS.