Update these dates in SQL Server to consecutive timestamps for each id?

Question

I have a table of IDs and dates. I need to turn the dates into datetimes, but each timestamp has to be unique within that ID. For example, if this is my table:

ID	Date
0001	2020-03-21
0001	2020-03-21
0001	2020-03-21
0002	2020-03-21
0002	2020-03-21

then I need my output to look like:

ID	Datetime
0001	2020-03-21 00:00:01
0001	2020-03-21 00:00:02
0001	2020-03-21 00:00:03
0002	2020-03-21 00:00:01
0002	2020-03-21 00:00:02

I'm pretty sure we can safely assume that no ID will have 86,400 entries on one day, so I shouldn't have to worry about the date rolling over by accident.

I'm thinking a cursor may be the answer, but I'm new to SQL and haven't had much luck figuring out how I'd use one for this. Here's what I tried so far, which didn't get me quite where I need to be:

SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY [CID] ASC,[XDATE] ASC) AS RN
,[CID]
,[XDATE]
,dateadd(second, ((ROW_NUMBER() OVER(ORDER BY [CID] ASC,[XDATE] ASC))%86400),cast([XDATE] as datetime)) AS NEW_DATE -- 86400 sec in a day so need to mod ROW_NUMBER to prevent changing the date
FROM entryTable

This query applies the datetime addition consecutively, meaning it will occasionally happen that the time rolls over at midnight on the same day and ID as the last entry. This causes the entries to be out of order in our final database, as it sorts the data based on the timestamp, and anything showing up as post-midnight will then come before everything else that actually came first in our current database.

Answer 1

While you may not have a single ID with more 86,400 entries per day, it sounds like you have enough IDs with enough entries to go over 86,400 total. In order to reset the row numbers like you show in the question, you need to apply a PARTITION to the window function:

SELECT TOP (1000) OverallRN = ROW_NUMBER() OVER (ORDER BY CID, XDATE)
,ROW_NUMBER() OVER
  (PARTITION BY CID ORDER BY [XDATE]) AS RN
,[CID]
,[XDATE]
,dateadd(second, ROW_NUMBER() OVER
  (PARTITION BY CID ORDER BY [XDATE]),cast([XDATE] as datetime)) AS NEW_DATE
FROM dbo.entryTable;

• Example db<>fiddle

If you still go over 86,400 entries for any given ID / day combination, you need to define what to do with 86,401+ because it certainly won't be mod (%) if you want to maintain the original sequence. Perhaps you need to go more granular than a second, like adding ROW_NUMBER() * 100 milliseconds. Although if those are the only two columns in the table, I don't know how you would know that anything is out of sequence.

Answer 2

use this

SELECT ID ,Date,
       concat(Date,' ',DATEADD(second, 
    CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) as int), 
    CAST('00:00:00' AS Time))) AS datetime
from entrytable

or better fit to your query

SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY [CID] ASC,[XDATE] ASC) AS RN
,[CID]
,[XDATE]
, concat(Date,' ',DATEADD(second, 
    CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) as int), 
    CAST('00:00:00' AS Time))) AS datetime
FROM entryTable