CODEWITHSUNDEEP
sql-servert-sqldatetimeprimary-key
Kritner
Kritner

Reputation: 13765

Primary Key violation on datetime with differing values

Let's ignore the fact that a datetime is being used as a primary key.

Given the following:

DECLARE @table table (test datetime primary key)

INSERT INTO @table
(
    test
)
select
    '2015-09-21 00:00:00.001' -- test - datetime
-- successful insert 

INSERT INTO @table
(
    test
)
select
    '2015-09-21 00:00:00.002' -- test - datetime
-- successful insert

INSERT INTO @table
(
    test
)
select
    '2015-09-21 00:00:00.003' -- test - datetime
-- fails due to primary key violation

select * from @table

SQL Fiddle with only first 2 inserts

SQL Fiddle with all 3 inserts, fails on third insert

What's going on here? All three "attempted" inserted values are distinct, but .002 and .003 are being considered "the same value"

Upvotes: 1

Views: 3449

Answers (4)

M.Ali
M.Ali

Reputation: 69524

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

enter image description here

Source MSDN SQL Server Datetime

Hence your table value 2015-09-21 00:00:00.002 is rounded to 2015-09-21 00:00:00.003 and on your last insert you get the primary key violation.

Datetime is probably one of the worst data types to have you primary key on. Find an alternative column in your table or add a surrogate column to act as a primary key column.

Upvotes: 6

paparazzo
paparazzo

Reputation: 45096

Those are not unique

Accuracy Rounded to increments of .000, .003, or .007 seconds

Maybe not use datetime as a PK

datetime (Transact-SQL)

Upvotes: 2

Rahul
Rahul

Reputation: 77876

Looks like it's not considering the milliseconds part differently but if you use DATETIME2 as datatype then it will succeed since DATETIME2 will produce a better precision. Try below:

DECLARE @table table (test datetime2 primary key)

INSERT INTO @table
(
    test
)
select
    '2015-09-21 00:00:00.001' -- test - datetime
-- successful insert 

INSERT INTO @table
(
    test
)
select
    '2015-09-21 00:00:00.002' -- test - datetime
-- successful insert

INSERT INTO @table
(
    test
)
select
    '2015-09-21 00:00:00.003'

Upvotes: 4

Sean Lange
Sean Lange

Reputation: 33581

The datetime datatype does not have that much precision. Try this query to see what happens with those values. The datetime datatype is only precise to .003. https://msdn.microsoft.com/en-us/library/ms187819.aspx

select CAST('2015-09-21 00:00:00.001' as datetime) union all
select '2015-09-21 00:00:00.002' union all
select '2015-09-21 00:00:00.003'

Upvotes: 12

Related Questions