View Full Version : adDBTimeStamp cutting off milliseconds

09-27-2007, 09:42 PM
First of all, I'm using ASP and SQL Server 2000

I have a stored procedure and I need to update a row that uses a datetime in the where clause (because the employee can have more than one registration):
UPDATE dbo.tblRegistration SET Reg_Status = @stat
WHERE Emp_Num = @emp_num AND Reg_DateTime = @reg_datetime

I have a recordset of employees that I am looping through and for each employee, I update their status using the stored procedure mentioned above. In this recordset, I am getting the Emp_Num and Reg_DateTime directly from the table (dbo.tblRegistration) and then feeding it back into the Update stored procedure through the use of an ADO Command with parameters.

So I pass the recordset Reg_DateTime into the command like this:
cmd.Parameters.Append cmd.CreateParameter("@Reg_DateTime", adDBTimeStamp, adParamInput,,r("Reg_DateTime"))

The problem is that the actual Reg_DateTime in the table has milliseconds and what comes in from the command using adDBTimeStamp seems to not have milliseconds so they don't match and the row does not get updated.

I tried to correct the problem by changing my stored procedure like this:
WHERE Emp_Num = @emp_num AND CONVERT(varchar(25), Reg_DateTime, 120) = CONVERT(varchar(25), @Reg_DateTime, 120) but that doesn't always work either because the milliseconds get rounded or truncated differently, like
Reg_DateTime becomes = to '2007-9-27 9:54:39' and @Reg_DateTime becomes = to '2007-9-27 9:54:40' so they still don't match and the row doesn't get updated. Does anyone have any ideas for this?