I don’t understand why DATEADD does not increase time.

Question

Migrating data from Access to SQL Server. The SQL Server table defines the inst_id, cons_code, and eff_date_time columns as the primary key. The eff_date_time data coming from access is not unique, so I tried to increase the seconds field by one second, so I will have a unique date-time. I cannot get DATEADD to increase the date by 1 second. Attached is my code. What am I doing wrong?

USE [CON-INST] GO DECLARE @cv_InstId VARCHAR(25), @cv_ConsCode VARCHAR(10), @cv_EffDateTime DATETIME, @lv_count INT DECLARE BumpDate_Cursor CURSOR STATIC FOR SELECT inst_id, cons_code, eff_date_time FROM [CON-INST].[dba].[constants_temp] ORDER BY inst_id OPEN BumpDate_Cursor FETCH FIRST FROM BumpDate_Cursor INTO @cv_InstId, @cv_ConsCode, @cv_EffDateTime SET @lv_count = 1 // Debug statements PRINT '@cv_InstId = ' + @cv_InstId PRINT '@cv_ConsCode = ' + @cv_ConsCode PRINT '@cv_EffDateTime = ' + CONVERT(VARCHAR, @cv_EffDateTime) PRINT '@lv_count = ' + CONVERT(VARCHAR, @lv_count) -- Loop to iterate thru instruments identifying the various constant types that are needed, ie the column names - constant 1, constant 2, constant 3, station, offset, etc. WHILE @@FETCH_STATUS = 0 -- do processing BEGIN PRINT '@lv_count before = ' + CONVERT(VARCHAR, @lv_count) PRINT CONVERT(VARCHAR, @cv_EffDateTime, 121) IF (CONVERT(VARCHAR, @cv_EffDateTime,121) = '1901-01-01 17:00:00.000') BEGIN UPDATE [CON-INST].[dba].[constants_temp] SET eff_date_time = DATEADD(second, @lv_count, eff_date_time) WHERE inst_id = @cv_InstId and cons_code = @cv_ConsCode; PRINT CONVERT(VARCHAR, @cv_EffDateTime, 121) END FETCH NEXT FROM BumpDate_Cursor INTO @cv_InstId, @cv_ConsCode, @cv_EffDateTime SET @lv_count = @lv_count + 1 PRINT '@lv_count after = ' + CONVERT(VARCHAR, @lv_count) END CLOSE BumpDate_Cursor DEALLOCATE BumpDate_Cursor 

Data in the table before updating inst_id cons_code eff_date_time constant entry_user enter code entry_date update_user update_date
1 PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 07: 33.770 NULL NULL
1 PU 1901-01-01 17: 00: 00.000 821.6 dba 2012-08-02 11: 07: 33.770 NULL NULL
1 PU 1901-01-01 17: 00: 00.000 8 dba 2012-08-02 11: 07: 33.770 NULL NULL
1 PU 1901-01-01 17: 00: 00.000 2251 dba 2012-08-02 11: 07: 33.770 NULL NULL
2 PU 1901-01-01 17: 00: 00.000 2251 dba 2012-08-02 11: 07: 33.770 NULL NULL
2 PU 1901-01-01 17: 00: 00.000 22 dba 2012-08-02 11: 07: 33.770 NULL NULL
2 PU 1901-01-01 17: 00: 00.000 820.9 dba 2012-08-02 11: 07: 33.773 NULL NULL
2 PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 07: 33.773 NULL NULL
3 PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 07: 33.773 NULL NULL
3 PU 1901-01-01 17: 00: 00.000 821.5 dba 2012-08-02 11: 07: 33.773 NULL NULL
3 PU 1901-01-01 17: 00: 00.000 8 dba 2012-08-02 11: 07: 33.773 NULL NULL
3 PU 1901-01-01 17: 00: 00.000 2095 dba 2012-08-02 11: 07: 33.773 NULL NULL
4 PU 1901-01-01 17: 00: 00.000 2095 dba 2012-08-02 11: 07: 33.777 NULL NULL
4 PU 1901-01-01 17: 00: 00.000 22 dba 2012-08-02 11: 07: 33.777 NULL NULL
4 PU 1901-01-01 17: 00: 00.000 820.5 dba 2012-08-02 11: 07: 33.777 NULL NULL
4 PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 07: 33.777 NULL NULL
A PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 07: 33.777 NULL NULL
A PU 1901-01-01 17: 00: 00.000 816.8 dba 2012-08-02 11: 07: 33.777 NULL NULL
A PU 1901-01-01 17: 00: 00.000 120.5 dba 2012-08-02 11: 07: 33.780 NULL NULL
A PU 1901-01-01 17: 00: 00.000 2255 dba 2012-08-02 11: 07: 33.780 NULL NULL
A-1 AS 1972-07-01 00: 00: 00.000 1492 dba 2012-08-02 11: 07: 33.780 NULL NULL
A-1 AS 1972-07-01 00: 00: 00.000 986.48 dba 2012-08-02 11: 07: 33.780 NULL NULL
A-1 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 07: 33.780 NULL NULL
A-10 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 07: 33.780 NULL NULL
A-10 AS 1972-07-01 00: 00: 00.000 986.48 dba 2012-08-02 11: 07: 33.780 NULL NULL
A-10 AS 1972-07-01 00: 00: 00.000 1857 dba 2012-08-02 11: 07: 33.780 NULL NULL
A-11 AS 1972-07-01 00: 00: 00.000 1896 dba 2012-08-02 11: 07: 33.783 NULL NULL
A-11 AS 1972-07-01 00: 00: 00.000 986.5 dba 2012-08-02 11: 07: 33.783 NULL NULL
A-11 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 07: 33.783 NULL NULL
A-12 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 07: 33.783 NULL NULL
A-12 AS 1972-07-01 00: 00: 00.000 986.5 dba 2012-08-02 11: 07: 33.783 NULL NULL
A-12 AS 1972-07-01 00: 00: 00.000 1936 dba 2012-08-02 11: 07: 33.783 NULL NULL

Output from PRINT statements at runtime

@cv_InstId = 1

@cv_ConsCode = PU

@cv_EffDateTime = January 1, 1901 5:00

@lv_count = 1

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 2

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 3

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 4

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 5

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 6

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 7

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 8

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 9

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 10

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 11

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 12

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 13

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 14

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 15

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 16

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 17

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 18

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 19

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 20

Before the update: 1901-01-01 17: 00: 00.000

@lv_count = 21

Before the update: 1972-07-01 00: 00: 00.000

@lv_count = 22

Before the update: 1972-07-01 00: 00: 00.000

@lv_count = 23

Before the update: 1972-07-01 00: 00: 00.000
@lv_count = 24

Before the update: 1972-07-01 00: 00: 00.000

@lv_count = 25

Before the update: 1972-07-01 00: 00: 00.000

Table result after execution
inst_id cons_code eff_date_time constant entry_user entry_date update_user update_date
1 PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 28: 27.287 NULL NULL
1 PU 1901-01-01 17: 00: 00.000 821.6 dba 2012-08-02 11: 28: 27.287 NULL NULL
1 PU 1901-01-01 17: 00: 00.000 8 dba 2012-08-02 11: 28: 27.290 NULL NULL
1 PU 1901-01-01 17: 00: 00.000 2251 dba 2012-08-02 11: 28: 27.290 NULL NULL
2 PU 1901-01-01 17: 00: 00.000 2251 dba 2012-08-02 11: 28: 27.290 NULL NULL
2 PU 1901-01-01 17: 00: 00.000 22 dba 2012-08-02 11: 28: 27.290 NULL NULL
2 PU 1901-01-01 17: 00: 00.000 820.9 dba 2012-08-02 11: 28: 27.290 NULL NULL
2 PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 28: 27.290 NULL NULL
3 PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 28: 27.290 NULL NULL
3 PU 1901-01-01 17: 00: 00.000 821.5 dba 2012-08-02 11: 28: 27.290 NULL NULL
3 PU 1901-01-01 17: 00: 00.000 8 dba 2012-08-02 11: 28: 27.290 NULL NULL
3 PU 1901-01-01 17: 00: 00.000 2095 dba 2012-08-02 11: 28: 27.293 NULL NULL
4 PU 1901-01-01 17: 00: 00.000 2095 dba 2012-08-02 11: 28: 27.293 NULL NULL
4 PU 1901-01-01 17: 00: 00.000 22 dba 2012-08-02 11: 28: 27.293 NULL NULL
4 PU 1901-01-01 17: 00: 00.000 820.5 dba 2012-08-02 11: 28: 27.293 NULL NULL
4 PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 28: 27.293 NULL NULL
A PU 1901-01-01 17: 00: 00.000 833.2 dba 2012-08-02 11: 28: 27.293 NULL NULL
A PU 1901-01-01 17: 00: 00.000 816.8 dba 2012-08-02 11: 28: 27.293 NULL NULL
A PU 1901-01-01 17: 00: 00.000 120.5 dba 2012-08-02 11: 28: 27.297 NULL NULL
A PU 1901-01-01 17: 00: 00.000 2255 dba 2012-08-02 11: 28: 27.297 NULL NULL
A-1 AS 1972-07-01 00: 00: 00.000 1492 dba 2012-08-02 11: 28: 27.297 NULL NULL
A-1 AS 1972-07-01 00: 00: 00.000 986.48 dba 2012-08-02 11: 28: 27.297 NULL NULL
A-1 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 28: 27.297 NULL NULL
A-10 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 28: 27.297 NULL NULL
A-10 AS 1972-07-01 00: 00: 00.000 986.48 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-10 AS 1972-07-01 00: 00: 00.000 1857 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-11 AS 1972-07-01 00: 00: 00.000 1896 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-11 AS 1972-07-01 00: 00: 00.000 986.5 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-11 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-12 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-12 AS 1972-07-01 00: 00: 00.000 986.5 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-12 AS 1972-07-01 00: 00: 00.000 1936 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-13 AS 1972-07-01 00: 00: 00.000 1976 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-13 AS 1972-07-01 00: 00: 00.000 986.46 dba 2012-08-02 11: 28: 27.300 NULL NULL
A-13 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 28: 27.303 NULL NULL
A-14 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 28: 27.303 NULL NULL
A-14 AS 1972-07-01 00: 00: 00.000 986.48 dba 2012-08-02 11: 28: 27.303 NULL NULL
A-14 AS 1972-07-01 00: 00: 00.000 2016 dba 2012-08-02 11: 28: 27.303 NULL NULL
A-15 AS 1972-07-01 00: 00: 00.000 0 dba 2012-08-02 11: 28: 27.303 NULL NULL

Answer 1

There are several problems with the code, but the main one is the update operator, which updates several records in the database at once. For example, when the loop starts the first update, it updates four records, where inst_id = '1' and const_code = 'PU', adding 1 second to each of them. In the second iteration, he adds 2 seconds for all four entries in the third iteration, and finally 4 seconds for all four entries in the fourth iteration (making all of them 1 + 2 + 3 + 4 = 10 seconds after 5:00).

The best solution is to add a new column, an identifier that is of type INT IDENITY PRIMARY KEY and discards the composite primary key in the inst_id, cons_code and eff_date_time columns.

However, if for some reason you should use a composite primary key in these three fields, then here is the code for this.

NOTE. You cannot just add a unique number to each date, because you can get to the point where you have made so many second increments that they actually come across a different value that you find in the table. This is the reason for the EXISTS code part.

 drop table [constants_temp] go drop table [constants_new] go create table [constants_temp] ( inst_id varchar(25), cons_code varchar(10), eff_date_time datetime ) insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('1', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('1', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('1', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('1', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('2', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('2', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('2', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('2', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('3', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('3', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('3', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('3', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('4', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('4', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('4', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('4', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A', 'PU', '1901-01-01 17:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-1', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-1', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-1', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-10', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-10', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-10', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-11', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-11', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-11', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-12', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-12', 'AS', '1972-07-01 00:00:00.000') insert into [constants_temp] (inst_id, cons_code, eff_date_time) values ('A-12', 'AS', '1972-07-01 00:00:00.000') -- Create a table with the idential structure as [constants_temp], except with the composite primary key on all three fields create table [constants_new] ( inst_id varchar(25), cons_code varchar(10), eff_date_time datetime, PRIMARY KEY (inst_id, cons_code, eff_date_time) ) -- Variables to hold the column values from each row in the cursor DECLARE @Cur_inst_id VARCHAR(25) DECLARE @Cur_cons_code VARCHAR(10) DECLARE @Cur_eff_date_time DATETIME -- Go through all of the records in the [constants_temp] table DECLARE BumpDate_Cursor CURSOR FOR SELECT inst_id, cons_code, eff_date_time FROM [constants_temp] ORDER BY inst_id, cons_code, eff_date_time -- Open the cursor and get the first record OPEN BumpDate_Cursor FETCH NEXT FROM BumpDate_Cursor INTO @Cur_inst_id, @Cur_cons_code, @Cur_eff_date_time -- For all the the records in the cursor... WHILE @@FETCH_STATUS = 0 BEGIN -- While there is already a record with a matching institution code, cons code, and effective date... WHILE EXISTS (SELECT inst_id FROM [constants_new] WHERE inst_id = @Cur_inst_id and cons_code = @Cur_cons_code and eff_date_time = @Cur_eff_date_time) BEGIN -- Keep incrementing the effective date by one second set @Cur_eff_date_time = DATEADD(second, 1, @Cur_eff_date_time) END -- Insert the new unique row INSERT INTO [constants_new] (inst_id, cons_code, eff_date_time) VALUES (@Cur_inst_id, @Cur_cons_code, @Cur_eff_date_time) -- Get the next record in the cursor FETCH NEXT FROM BumpDate_Cursor INTO @Cur_inst_id, @Cur_cons_code, @Cur_eff_date_time END -- Close and deallocate the cursor CLOSE BumpDate_Cursor DEALLOCATE BumpDate_Cursor -- Show the results select * from [constants_new] order by inst_id, cons_code, eff_date_time