Adding miliseconds to a datetime in tsql INSERT INTO

Question

I'm doing a INSERT INTO query in order to initialize a new table. The primary key is RFQ_ID and Action_Time

How could add 1 milisecond to each Action_Time on new record in order to avoid "Violation of PRIMARY KEY constraint"

INSERT INTO QSW_RFQ_Log
(RFQ_ID, Action_Time, Quote_ID, Note)
SELECT 
  RFQ_ID
  , GETDATE() AS Action_Time
  , Quote_ID
  , 'Added to RFQ on Initialization' AS Note
FROM QSW_Quote

Answer 1

Use DATEADD, although having re-read your question I'm not entirely sure why you are encountering that problem if you are inserting single rows at a time (and, as others have pointed out, the real issue seems to be your PK):

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111'
SELECT '1 millisecond' ,DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT '2 milliseconds', DATEADD(millisecond,2,@datetime2)
UNION ALL
SELECT '1 microsecond', DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT '2 microseconds', DATEADD(microsecond,2,@datetime2)
UNION ALL
SELECT '49 nanoseconds', DATEADD(nanosecond,49,@datetime2)
UNION ALL
SELECT '50 nanoseconds', DATEADD(nanosecond,50,@datetime2)
UNION ALL
SELECT '150 nanoseconds', DATEADD(nanosecond,150,@datetime2);
/*
Returns:
1 millisecond     2007-01-01 13:10:10.1121111
2 milliseconds    2007-01-01 13:10:10.1131111
1 microsecond     2007-01-01 13:10:10.1111121
2 microseconds    2007-01-01 13:10:10.1111131
49 nanoseconds    2007-01-01 13:10:10.1111111
50 nanoseconds    2007-01-01 13:10:10.1111112
150 nanoseconds   2007-01-01 13:10:10.1111113
*/

Answer 2

As suggested above, use DateAdd() function, as:

INSERT QSW_RFQ_Log  (RFQ_ID, Action_Time, Quote_ID) 
SELECT RFQ_ID, DateAdd(ms, 1, GETDATE()) Action_Time,  
Quote_ID, 'Added to RFQ on Initialization' AS Note 
FROM QSW_Quote 

But yr insert statement lists only three fields to be inserted into, but has four values to be inserted, so there's another problem there...

Answer 3

I think the real problem is that RFQ_ID, Action_Time shouldn't be a primary key. Create a surrogate primary key and put a non-unique index on RFQ_ID, Action_Time.

Update: If you really want to stick with your existing design you could do what you asked but using 10 milliseconds instead of one millisecond between each row, to compensate for the low precision of datetime. You can use the row number to determine how many milliseconds to add so that you get a different timestamp for each row:

INSERT INTO QSW_RFQ_Log
(RFQ_ID, Action_Time, Quote_ID, Note)
SELECT
  RFQ_ID,
  DATEADD(millisecond, 10 * ROW_NUMBER() OVER (ORDER BY Quote_ID), GETDATE()) AS Action_Time,
  Quote_ID,
  'Added to RFQ on Initialization' AS Note
FROM QSW_Quote

Answer 4

I'd agree with Mark Byers' answer as the real solution. Just wanted to add a gotcha, that prior to SQL Server 2008, the datetime accuracy is to about 3.33ms. Quote from MSDN:

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

So adding 1ms to dates will not solve your problem.

e.g.

SELECT DATEADD(ms, 1, '2010-04-12T12:00:00.000') -- outputs time still as x.000s
SELECT DATEADD(ms, 2, '2010-04-12T12:00:00.000') -- output: .003s
SELECT DATEADD(ms, 3, '2010-04-12T12:00:00.000') -- output: .003s
SELECT DATEADD(ms, 4, '2010-04-12T12:00:00.000') -- output: .003s
SELECT DATEADD(ms, 5, '2010-04-12T12:00:00.000') -- output: .007s
SELECT DATEADD(ms, 6, '2010-04-12T12:00:00.000') -- output: .007s
SELECT DATEADD(ms, 7, '2010-04-12T12:00:00.000') -- output: .007s
SELECT DATEADD(ms, 8, '2010-04-12T12:00:00.000') -- output: .007s
SELECT DATEADD(ms, 9, '2010-04-12T12:00:00.000') -- output: .010s

You'd actually be needed to add 3ms each time. At best it would work for your situation, but just not really be what feels like a "clean" solution, a bit of hack. At worst, it just wouldn't work/scale, depending on the data volumes/the density of the data spread. But, you should be aware of the datetime accuracy gotcha if you head down this route.

SQL Server 2008 does introduce DATETIME2 which has an accuracy of 100ns. See DaveK's answer.

Answer 5

ever heard of "robing Peter to pay Paul"?

you can manipulate the time, which is a real hack, to get the insert to work, but what about any concurrent inserts, they will have overlapping times. Perhaps this will never happen based on your design, but I've learned over the years to never say never.

This is the exact reason why I never make a datetime a PK, they are not always unique.

I need action time as a primary key, period.

WHY?

You can make an identity be the PK and still have the clustered index on RFQ_ID+Action_Time+identity and how would that really affect your design or any performance? it would also better reflect that the data was added simultaneously (each row with the same datetime)