473,320 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Server 2000 and Milliseconds (datetime data type)

I'm running into a constant issue of SQL Server modifying the
millisecond part of a timestamp insert from another application. The
application inserts timestamp which includes a millisecond portion as a
string (varchar). But when an SQL Server moves this data to another
table (for reporting), the string is inserted in a datetime field, the
millisecond field invariably changes by 1-2 milliseconds for every
single data point inserted. Given the time critical nature of this data
(to a millisecond), its almost impossible to avoid this other than to
leave the data as string type. But this drives the analytical reporting
folks wild as report queries based on time criteria are getting messed
up. Any ideas how to force SQL Server not to mess around with the
millisecond value? Does this problem exist with SQL Server 2005 as well?

Jan 18 '07 #1
2 8217
This is a documented behavior of the datetime datatype. As the Books
On Line says: "Values are rounded to increments of .000, .003, or .007
seconds". It applies to all versions, and I would not expect it to
ever change.

If you must keep it to the exact millisecond then you can not use
datetime.

You could split the information into two columns, say one part for the
date (could use smalldatetime) and the other for milliseconds since
midnight. Or, since smalldatetime is to the minute the second column
would just have seconds and milliseconds. There are countless
variations possible, none will make processing simple.

Roy Harvey
Beacon Falls, CT

On 18 Jan 2007 11:15:14 -0800, vi***********@gmail.com wrote:
>I'm running into a constant issue of SQL Server modifying the
millisecond part of a timestamp insert from another application. The
application inserts timestamp which includes a millisecond portion as a
string (varchar). But when an SQL Server moves this data to another
table (for reporting), the string is inserted in a datetime field, the
millisecond field invariably changes by 1-2 milliseconds for every
single data point inserted. Given the time critical nature of this data
(to a millisecond), its almost impossible to avoid this other than to
leave the data as string type. But this drives the analytical reporting
folks wild as report queries based on time criteria are getting messed
up. Any ideas how to force SQL Server not to mess around with the
millisecond value? Does this problem exist with SQL Server 2005 as well?
Jan 18 '07 #2
I should have known that! Darn...

Thanks.

On Jan 18, 3:58 pm, Roy Harvey <roy_har...@snet.netwrote:
This is a documented behavior of the datetime datatype. As the Books
On Line says: "Values are rounded to increments of .000, .003, or .007
seconds". It applies to all versions, and I would not expect it to
ever change.

If you must keep it to the exactmillisecondthen you can not use
datetime.

You could split the information into two columns, say one part for the
date (could use smalldatetime) and the other for milliseconds since
midnight. Or, since smalldatetime is to the minute the second column
would just have seconds and milliseconds. There are countless
variations possible, none will make processing simple.

Roy Harvey
Beacon Falls, CT

On 18 Jan 2007 11:15:14 -0800, vikram.man...@gmail.com wrote:
I'm running into a constant issue ofSQLServermodifying the
millisecondpart of a timestamp insert from another application. The
application inserts timestamp which includes amillisecondportion as a
string (varchar). But when anSQLServermoves this data to another
table (for reporting), the string is inserted in a datetime field, the
millisecondfield invariably changes by 1-2 milliseconds for every
single data point inserted. Given the time critical nature of this data
(to amillisecond), its almost impossible to avoid this other than to
leave the data as string type. But this drives the analytical reporting
folks wild as report queries based on time criteria are getting messed
up. Any ideas how to forceSQLServernot to mess around with the
millisecondvalue? Does this problem exist withSQLServer2005 as well?- Hide quoted text -- Show quoted text -
Jan 30 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ken Lindner | last post by:
I have a need to become familiar with SQL Server 2000 for work. Needless to say I am new to SQL Server any version, but not IT in general. My employer has provided me with the SQL Server 2000...
5
by: Dave Pylatuk | last post by:
Hello all. I am attempting to insert a row into a table with a datetime column: When the insert statement contains a value for the millisecond portion of the data time column: ie. {ts...
3
by: Maurizio Fazio | last post by:
I have a Sql Server 2000 installation and my server is Win2k sp2 English. If i run a query with WHERE condition, for example, SELECT * FROM TABLE WHERE DATEOFBIRTH ='2004-01-15' my resultset...
4
by: Bon | last post by:
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to...
5
by: sandman | last post by:
I've been testing my web app on another workstation to simulate using the server time. The test pc's time is an hour behind the server time and when the user processes a request, the server time...
3
by: NateM | last post by:
How do I convert any given date into a milliseconds value that represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT? Is there an easy way to do this like...
3
by: aling | last post by:
Execute following T-SQL within Queary Analyzer of SQL Server 2000: ======================================= DECLARE @dTest DATETIME SET @dTest='2001-1-1 1:1:1:991' SELECT @dTest SET...
4
by: Manikandan | last post by:
Hi, I'm inserting a datetime values into sql server 2000 from c# SQL server table details Table name:date_test columnname datatype No int date_t DateTime ...
3
by: huohaodian | last post by:
Hi, How can I convert a value created from DateTime.Now() to the datetime format that SQL Server recognises? Thanks in advance.
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.