Can't populate SQLServer datetime column with milliseconds

[Joe Rattz]

I have a table with a datetime column datatype. I am trying to determine
the format for entering the data for this type and I would like to include
milliseconds and am having no luck.

If I enter the following in Enterprise Manager for the datatime column:

05/05/2004 12:01:01

The column accepts that fine.

But, if I try either

05/05/2004 12:01:01.000

or

05/05/2004 12:01:01:000

I get an error message saying:

"The value you entered is not consistent with the data type or length of
the column."

Anyone know what the date/time string needs to look like for a datetime if
you want to include milliseconds?

Thanks.

 

[Joe Rattz]

Ok, this is weird. If I create the table in Query Analyzer and insert a
datetime with milliseconds, the record is there just fine:

create table dttest ( d datetime not null )
go

insert into dttest
select '05/05/2004 15:46:00.450'

select * from dttest

d
------------------------------------------------------
2004-05-05 15:46:00.450

And the milliseconds display just fine. If I examine the record in
Enterprise Manager, it truncates or rounds to the nearest second.

5/5/2004 3:46:00 PM

And, the problem must not be restricted to EM because I can't insert the
record programattically either using the .NET SqlConnection/SqlCommand
classes.

 

[Patrice]

Looks like a precision problem :
- AFAIK SQL Server stores up to 3 milliseconds
- EM display is likely just a formating problem (as in most cases you don't
need such a precision)
- check also if the .NET datatype is able to store such a small quantity

Patrice

 

[Joe Rattz]

This is resolved. My actual needs did not involve EM, but I resorted to
using EM to try to discover my problem. Since EM could not handle the
milliseconds, it made me think the problem was lower level. In reality, the
problem I was having was in my code. But, since I couldn't get the data
into the database using EM either, I thought the actual problem was
elsewhere.

Thanks.