datetime-typed column insertion conflict

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

datetime-typed column insertion conflict

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-13 : 11:43:12

Hi,
I have a simple SQL queries, and I do not understand why there are some time-partion values of datetime-typed column "Dates" do not match with what I expect in INSERT statements.

Thank you for your help.


declare @t table (clientid int,  Unitbalance numeric(21,0), Dates datetime)

insert into @t values (123, 999, '1 jan 2008 12:00:01:001')
insert into @t values (456, 888, '1 jan 2008 12:00:01:006')
insert into @t values (456, 777, '1 jan 2008 12:00:01:007')
insert into @t values (789, 222, '1 jan 2008 12:00:01:008')
insert into @t values (789, 333, '1 jan 2008 12:00:01:009')
insert into @t values (123, 999, '1 jan 2008 12:00:01:010')
insert into @t values (123, 111, '1 jan 2008 12:00:01:011')

-- work
select * from @t

And there are some time-portion values in the datetime-typed "Dates" column do not match with previpusly input using INSERT


clientid    Unitbalance             Dates                                                  
----------- ----------------------- ------------------------------------------------------ 
123         999                     2008-01-01 12:00:01.000
456         888                     2008-01-01 12:00:01.007
456         777                     2008-01-01 12:00:01.007
789         222                     2008-01-01 12:00:01.007
789         333                     2008-01-01 12:00:01.010
123         999                     2008-01-01 12:00:01.010
123         111                     2008-01-01 12:00:01.010

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-13 : 11:54:12

This is normal behavior. This happens because SQL Server stores DATETIME with a precision of 3.33 milliseconds and hence any value in between this precision will be rounded to nearest fraction.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-13 : 13:45:26

quote:
Originally posted by harsh_athalye

This is normal behavior. This happens because SQL Server stores DATETIME with a precision of 3.33 milliseconds and hence any value in between this precision will be rounded to nearest fraction.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thank for your reply. Could you mind providing me a way to insert values I expect to its most precise time values?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:58:16

quote:
Originally posted by johnsql

quote:
Originally posted by harsh_athalye

This is normal behavior. This happens because SQL Server stores DATETIME with a precision of 3.33 milliseconds and hence any value in between this precision will be rounded to nearest fraction.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thank for your reply. Could you mind providing me a way to insert values I expect to its most precise time values?

SQL Server can store datetime values only upto precision of 3.33 milliseconds as suggested by Harsh

http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources