Whiel Update ,datetime value is rounded to seconds.! i want milliseconds too

Question

Hi friends,

While updating a datatime column in a table from another table, i noticed that mnilliseconds value are not shown.. instead it is rounded and the value is updated to nearest seconds.

Example :

Original Value: 2008-06-26 14:06:36.643

Updated Value : 2008-06-26 14:07:00

Please help me getting the actual value including milliseconds

Answer 1

In SQL Server;

SELECT CAST('2008-06-26 14:06:36.643' AS SMALLDATETIME)

> 2008-06-26 14:07:00

So the destination table column is probably SMALLDATETIME (or your casting in the query).

Answer 2

In the case where you're doing a straight update of a datetime in one table with one from another table (i.e. no fiddling with the value), then it sounds like the datatype in the table being updated is not the same.

i.e. in SQL Server world, it could be that you are using SMALLDATETIME column in the table being updated, but a DATETIME field in the table being copied from. SMALLDATETIME is only accurate to the second and so would show this behaviour