Article ID: 875225
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?
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 ***/
SET NOCOUNT ON
** Declare some local variables.
DECLARE @tblname varchar(255)
DECLARE @colname varchar(255)
DECLARE @execute_statement varchar(255)
** Declare the cursor
DECLARE TheCursor CURSOR FOR
select tbl.name, col.name
from syscolumns col
, sysobjects tbl
where col.type = 61
and tbl.type = 'U'
and col.id = tbl.id
order by 1
** Open the cursor.
** Standard fetch loop:
** Read the next row.
** If it is a row that has been delete since the cursor ** was opened, bypass it.
** If past the last row,
** you are done.
** Process the row.
SELECT 'Executing the following statements:'
SELECT ' '
WHILE @@FETCH_STATUS = @@FETCH_STATUS - That is,'WHILE TRUE'.
FETCH NEXT FROM TheCursor INTO @tblname, @colname
IF @@FETCH_STATUS = -2 -- Row has been deleted.
IF @@FETCH_STATUS = -1 -- All rows processed.
SELECT @execute_statement = "update " + @tblname +
" set " + @colname + " = dateadd(millisecond,datepart(millisecond,"
+ @colname + ")*-1," + @colname + ")" +
" where datepart(millisecond," + @colname + ") <> 0"
SELECT str(@@rowcount) + " Rows where updated"
SELECT '********** END OF JOB **********'
** Close and deallocate the cursor.
SET NOCOUNT off
This article was TechKnowledge Document ID: 9261