SQL Query DataTime value Inserting twice into the table

I am writing a simple csv开发者_开发问答 parser which reads the data from my csv and insert that into my table.Programs works fine When i test with small dataset.Where as when i go for larger one my datatime value alone is shown twice in the table row .dont know why??

For example:In the below picture you can see it prints the datetime twice which is supposed to print only once.But my other column data were rite .

CREATE TABLE [dbo].[SAMPLE](
    [Number] [int] NULL,
    [DateTime] [datetime] NULL,
    [Value] [decimal](9, 2) NULL
) ON [PRIMARY]

And I am running a simple query which runs nearly 45000 times.

string sqlQuery = "insert into Sample values ( " + number+ ",'" + row.dateT + "'," + value + ");"; 
SqlCommand sqlComm  = new SqlCommand(sqlQuery, dbcon);

To Test my input value I had written the "sqlquery" into a text file which is perfect and I am 100% sure I am not writing same datetime into table

DateTime values do not work (or are, at the very least, are very unlikely to work) as unique identifiers. Consider using an integral value with an identity property to achieve uniqueness.

Expecting the date/time of an insert/update to provide a unique datetime value for each row is a path to sadness, for a couple of reasons.

• First, the precision of a SQL Server datetime value is 1ms. Internally, a SQL Server datetime value is a tuple containing two 32-bit integers. The first integer is the count of days since the epoch (for SQL Server, that is 1 January 1900). The second is the offset in milliseconds from start of day (00:00:00.000 aka 12:00am). In the world of modern computer processors 1 millsecond is a loooong time. You are likely to get collisions, just for that reason.

• Second, the granularity of the SQL DateTime value is approximately 3ms (in actuality, the datetime value gets "rounded" into 3ms or 4ms buckets, further increasing the probably of a collision.

  • Finally (and I don't know that this is, in fact, true), I believe that if you insert multiple rows in a single transaction (e.g., insert foo select * from bar) and using a default property to set the current date/time, you may well wind up with everything tagged with the same datetime value as the transaction is considered completed simultaneously.

The "rounded" value will also be a multiple of 0, 3, or 7 milliseconds. Consider the following script. It creates a temp table, inserts some date/time string and converts them to datetime values:

drop table #temp
go
create table #temp
(
  value       varchar(32) not null primary key clustered ,
  dtConverted as convert(datetime,value,121)
)
go
set nocount on

insert #temp (value) values( '2011-01-31 23:59:59.000' )
insert #temp (value) values( '2011-01-31 23:59:59.001' )
insert #temp (value) values( '2011-01-31 23:59:59.002' )
insert #temp (value) values( '2011-01-31 23:59:59.003' )
insert #temp (value) values( '2011-01-31 23:59:59.004' )
insert #temp (value) values( '2011-01-31 23:59:59.005' )
insert #temp (value) values( '2011-01-31 23:59:59.006' )
insert #temp (value) values( '2011-01-31 23:59:59.007' )
insert #temp (value) values( '2011-01-31 23:59:59.008' )
insert #temp (value) values( '2011-01-31 23:59:59.009' )
insert #temp (value) values( '2011-01-31 23:59:59.010' )

insert #temp (value) values( '2011-01-31 23:59:59.990' )
insert #temp (value) values( '2011-01-31 23:59:59.991' )
insert #temp (value) values( '2011-01-31 23:59:59.992' )
insert #temp (value) values( '2011-01-31 23:59:59.993' )
insert #temp (value) values( '2011-01-31 23:59:59.994' )
insert #temp (value) values( '2011-01-31 23:59:59.995' )
insert #temp (value) values( '2011-01-31 23:59:59.996' )
insert #temp (value) values( '2011-01-31 23:59:59.997' )
insert #temp (value) values( '2011-01-31 23:59:59.998' )
insert #temp (value) values( '2011-01-31 23:59:59.999' )

set nocount off
go
select * from #temp
go

When you run the above script, the results are probably not what you might expect. Not only are the values rounded to 3ms or 4ms intervals, but any datetime value with a millisecond value greater than 997 gets rounded up to the next day (and month, in this case). That's a boundary condition that will bite you in real world scenarios (don't ask me how I know this). Here's the results, you'll get:

value                   dtConverted
----------------------- -----------------------
2011-01-31 23:59:59.000 2011-01-31 23:59:59.000
2011-01-31 23:59:59.001 2011-01-31 23:59:59.000
2011-01-31 23:59:59.002 2011-01-31 23:59:59.003
2011-01-31 23:59:59.003 2011-01-31 23:59:59.003
2011-01-31 23:59:59.004 2011-01-31 23:59:59.003
2011-01-31 23:59:59.005 2011-01-31 23:59:59.007
2011-01-31 23:59:59.006 2011-01-31 23:59:59.007
2011-01-31 23:59:59.007 2011-01-31 23:59:59.007
2011-01-31 23:59:59.008 2011-01-31 23:59:59.007
2011-01-31 23:59:59.009 2011-01-31 23:59:59.010
2011-01-31 23:59:59.010 2011-01-31 23:59:59.010
2011-01-31 23:59:59.990 2011-01-31 23:59:59.990
2011-01-31 23:59:59.991 2011-01-31 23:59:59.990
2011-01-31 23:59:59.992 2011-01-31 23:59:59.993
2011-01-31 23:59:59.993 2011-01-31 23:59:59.993
2011-01-31 23:59:59.994 2011-01-31 23:59:59.993
2011-01-31 23:59:59.995 2011-01-31 23:59:59.997
2011-01-31 23:59:59.996 2011-01-31 23:59:59.997
2011-01-31 23:59:59.997 2011-01-31 23:59:59.997
2011-01-31 23:59:59.998 2011-01-31 23:59:59.997
2011-01-31 23:59:59.999 2011-02-01 00:00:00.000

(21 row(s) affected)

Good luck!

You can try to execute the query inside a lock scope. This is just a guess though.

Try this:

select datetime, count(1) as countDT 
from SAMPLE
group by datetime
having count(1) > 1

That will tell you whether you have the same DateTime value twice or not.

Thanks for your answers . I have identified that its creating problem only because of DataTime format which we convert into string for format change . I inserted datatime as SqldbType.DateTime . Snippet of my code is given below which solved all my problem .

using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          int 123;

          cmd.Parameters.Add(new SqlParameter(@"Number", SqlDbType.int)).Value = 123;
          cmd.Parameters.Add(new SqlParameter(@"aDateTime", SqlDbType.DateTime)).Value = mydate;
cmd.Parameters.Add(new SqlParameter(@"aDateTime", SqlDbType.decimal)).Value = value;
          cmd.ExecuteNonQuery();
        }