truncate_timestamp_values option [database] [MobiLink client]

Limits the resolution of timestamp values.

Allowed values

On, Off

Default

Off

Scope

Can be set for the PUBLIC group only. DBA authority required. This option should not be enabled for databases already containing timestamp data.

Remarks

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 prior to 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.

In most cases, 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 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.

Example

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.