datetime-typed column insertion conflict

• johnsql-193053

  SSCommitted

  Points: 1657

  More actions

  June 13, 2008 at 9:53 am

  #187099

  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

• Raghuram (AJ)

  Ten Centuries

  Points: 1214

  More actions

  June 13, 2008 at 2:25 pm

  #828804

  Interesting one. The reason could be due to the rounding off. BOL says:

  "datetime values are rounded to increments of .000, .003, or .007 seconds"

  The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner

• Jeff Moden

  SSC Guru

  Points: 1004617

  More actions

  June 13, 2008 at 9:31 pm

  #828868

  Correct... the DATETIME datatype has a resolution of 3.3 milliseconds.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)