A TIMESTAMP value is precise to six decimal places in SQL Anywhere. However, to maintain compatibility with other software,
which may truncate the TIMESTAMP value to three decimal places, you can set the truncate_timestamp_values option to On to
limit the number of decimal places SQL Anywhere stores. The default_timestamp_increment option determines the number of decimal
places to which the TIMESTAMP value is truncated.
For MobiLink synchronization, if you are going to set this option, it must be set before performing the first synchronization.
If the database server finds TIMESTAMP values with a higher resolution than that specified by the combination of truncate_timestamp_values
and default_timestamp_increment, an error is reported.
Unloading the database and then reloading it into a new database in which the truncate_timestamp_values and default_timestamp_increment
values have been set is usually the easiest solution to ensure the proper TIMESTAMP values are used. However, depending on
the type of TIMESTAMP columns in your table, you can also do the following:
If the TIMESTAMP columns are defined with DEFAULT TIMESTAMP or DEFAULT UTC TIMESTAMP (so that the value is automatically updated
by the database server when the row is modified), you must delete all the rows in the table before the truncate_timestamp_values
option is changed. You can delete the rows using the DELETE or TRUNCATE TABLE statement.
If the TIMESTAMP column is defined with a value other than DEFAULT TIMESTAMP or DEFAULT UTC TIMESTAMP, you can execute an
UPDATE statement that casts the values to a string and then back to a TIMESTAMP. For example,
UPDATE T
SET ts = CAST( DATEFORMAT( ts, 'yyyy/mm/dd hh:nn:ss.ss' )
AS TIMESTAMP );
Note that this process may lose more precision than is necessary. The format string to use depends on the number of digits
of precision to be kept.
Setting the default_timestamp_increment option to 100000 causes truncation after the first decimal place in the seconds component,
allowing a value such as '2000/12/05 10:50:53:700' to be stored.
Allowed values