Don't understand why DATEADD is not incrementing datatime

Viewed 396

Migrating data from Access into SQL Server. SQL Server table defines inst_id, cons_code, and eff_date_time column as primary key. The eff_date_time data coming from access is not unique so I was attempting to increment the seconds field by one second so I would have a unique datetime. I can't get DATEADD to increment 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, i.e. 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 table prior to update 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 during execution

@cv_InstId = 1

@cv_ConsCode = PU

@cv_EffDateTime = Jan 1 1901 5:00PM

@lv_count = 1

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

@lv_count = 2

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

@lv_count = 3

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

@lv_count = 4

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

@lv_count = 5

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

@lv_count = 6

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

@lv_count = 7

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

@lv_count = 8

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

@lv_count = 9

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

@lv_count = 10

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

@lv_count = 11

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

@lv_count = 12

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

@lv_count = 13

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

@lv_count = 14

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

@lv_count = 15

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

@lv_count = 16

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

@lv_count = 17

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

@lv_count = 18

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

@lv_count = 19

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

@lv_count = 20

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

@lv_count = 21

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

@lv_count = 22

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

@lv_count = 23

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

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

@lv_count = 25

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

Table Output 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

1 Answers
Related