Removing milliseconds from datetime fields in SQL Server

Article ID: 875225
Expand all | Collapse all

TechKnowledge Content

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 ***/

USE SMS
SET NOCOUNT ON
GO

/*
** 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.
*/

OPEN TheCursor

/*
** 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'.
BEGIN
FETCH NEXT FROM TheCursor INTO @tblname, @colname

IF @@FETCH_STATUS = -2 -- Row has been deleted.
CONTINUE
IF @@FETCH_STATUS = -1 -- All rows processed.
BREAK

SELECT @execute_statement = "update " + @tblname +
" set " + @colname + " = dateadd(millisecond,datepart(millisecond,"
+ @colname + ")*-1," + @colname + ")" +
" where datepart(millisecond," + @colname + ") <> 0"

PRINT @execute_statement
EXEC (@execute_statement)
SELECT str(@@rowcount) + " Rows where updated"
END

SELECT '********** END OF JOB **********'

/*
** Close and deallocate the cursor.
*/

CLOSE TheCursor
DEALLOCATE TheCursor
GO

SET NOCOUNT off
GO





This article was TechKnowledge Document ID: 9261

Properties

Article ID: 875225 - Last Review: July 25, 2011 - Revision: 2.0
Keywords: 
kbmbsmigrate KB875225

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com