This article has been archived. It is offered "as is" and will no longer be updated.
Question: I have noticed that in my SQL Server tables that contain datetime fields, these fields contain values in the milliseconds portion of the time. I know that Dexterity time fields do not use milliseconds. How could this have happened, and how can I remove the milliseconds from my data?
Answer: Prior to Dexterity 3.00, the systime() function would incorrectly update milliseconds in time fields when used. Faircom c-tree multi-user time fields also store milliseconds. Therefore, if you used the systime() function in Dexterity 2.X to populate time fields, its possible that your data would include milliseconds. Many developers have moved data to SQL Server in 3.0 using several different tools. In many cases, the tools would retain the milliseconds when moving that data. Therefore, the end results was that datetime fields in SQL Server would include milliseconds, even though the fields normally would not.
Having the milliseconds with values can cause problems when trying to update records in which the time field was actually changed. You can encounter RECORDCHANGED errors when performing such actions. To clear these milliseconds, you can write Dexterity code to convert the time. This code uses the hour(), minute() and second() functions to parse the existing time field, and then to uses the mktime() function to set the proper time back into the table. This new time would then no longer include milliseconds.
You could execute a SQL Script that would also remove the milliseconds. The following sample script sets the milliseconds of all datetime columns to zero.
/*** Process to set the milliseconds of all datetime columns to zero ***/