How to store actual millisecond in datetime?

Hello. I've seached the this forum and others for this question of mine but couldn't get any thing. Everyone know that in MSSQL, the precision of datetime is currently limited to 1/300th of a second. My question is, is there any way to store the actual millisecond in the a table? Also, later, able to pull it up from the table? What are the options?

Please advise.

Thanks,

Rick..

Running the following script worked for me in storing milliseconds. You can review BOL under "Date and Time Formats" for a bit more clarity.

create table test1..t2(c1 datetime, c2 dec(17,12));

insert t2(c1)
select convert(DATETIME, '12/21/07 03:04:05.300');

insert t2(c1)
select getdate();

select c1, convert(dec(17,12), c1) from t2;

update t2
set c2 = convert(dec(17,12), c1);

|||

Hi Zach. Thank for replying. Actually, the problem is still there. If you replace the date you are storing to '10/25/2006 23:59:59.991' and run the script. You will get '10/25/2006 23:59:59.990'. I need to be able to store the exact millisecond being written. This rounding being done to the millisecond is causing use a lot of headache.

Any other idea?

Thanks again.

Rick..

|||Your problem is the Windows clock, which is what you are reading, is only updated every 55 ms. You will never get the EXACT ms something ran. The time is not that acurate.

Whatever you are trying to do, you will need to find another method to determine the order or timing.|||

Hi. I am not sure if your statement was true. I don't think it has anything to do with windows clock. Reason I am saying that was, the date time value I am saving into the table is hardcoded. Everyone know its how Microsoft implemented it. But I need to save the actual value without MSSQL truncating the value before storing it in the database.

Thanks for your comment though.

Rick..

|||I think that it is more of an issue of how Sybase originally defined datetime in SQL Server -and Microsoft has never corrected the 'mistake'.|||

The problem is that you never see the clock value but only the value converted to the binary format of a SQL datetime (which has the level of precision inherent in its structure).

If you really want to get at the unaltered clock value then I think that you need to use code to access it directly. If you are using SQL Server 2005 then you could write CLR code to access the System.DateTime structure. You could probably even create a new data type. Of course you need some way to hold and retrieve the information you want (possibly the ISO 8601 string format yyyy-mm-ddThh:mm:ss.mmm). I don't know the details of attempting this but those in the ".NET Framework inside SQL Server" forum could probably help.

If you are using an earlier version of SQL Server then you probably need to look at extended stored procedures.

|||Open a query window and repeat this statement 500 times:

PRINT CONVERT(varchar(30),getdate(),109)

Run this and see what you get.

You will get the exact same ms until it instantly changes to another set.

It is also a problem, someone mentioned, the significant digits of the time does not allow you to get proper ms.

You still need to find another way to do what you are trying to do.|||

Unfortunately, SQL Server is doing rounding with the datetime data type. Therefore, you won't be able to use this data type. Even if you store the value in a decimal(p,s) data type, you probably won't be able to cast it into a datetime data type without incurring problems.

Oracle implemented true precision, user configurable up to nine (9) decimal places, when it released its TIMESTAMP data type in 9i.

|||

Oddly enough try running the following entries:

select convert(DATETIME, '12/21/07 03:04:05.302')

select convert(DATETIME, '12/21/07 03:04:05.303')

select convert(DATETIME, '12/21/07 03:04:05.304')

select convert(DATETIME, '12/21/07 03:04:05.305')

You will find the following results:

2007-12-21 03:04:05.303

2007-12-21 03:04:05.303

2007-12-21 03:04:05.303

2007-12-21 03:04:05.307

So the next question is, what is going on? This is some mighty weird rounding problems. What we believe to be happening is milliseconds are translated into ticks at 3 ms increments.

Moral of the story, if you really need to save that low then you need to create an int column separate of the datetime column and save the milliseconds there.

|||That is not strange at all. That is caused by the precision of the time in the datetime field.

See this very good explanation:

http://www.sql-server-performance.com/bs_date_time.asp

You are NEVER going to get 1 ms resolution in a datetime field.|||

This MSDN article specifically states an accuracy of 3.33 milliseconds. I only meant it was strange in that it happens because of converting from varchar like '3/28/2007 11:30:00:001' to a datetime value.

http://msdn2.microsoft.com/en-us/library/ms187819.aspx