Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Unanswered: Conversion Error

    Using SQL 2005. Getting the following error. Using a cursor to update a table. I need to pass through the table two different times. The fields in the table update fine on the first time through, but on the 2nd time through I get the following error:
    Conversion failed when converting datetime from character string.


    Thank you for your help. David

    FETCH NEXT FROM DEQ INTO
    @GRGR_ID,
    @SGSG_ID,
    @SBSB_ID,
    @PASS1_GENERATION_DATE,
    @PASS1_TOTAL_AMOUNT_DUE,
    @STATUS_FLAG,
    @STATUS_FLAG_INSERT_DT

    WHILE @@FETCH_STATUS = 0

    BEGIN
    UPDATE dbo.RPT_DELINQUENCY_TEST
    SET PASS1_GENERATION_DATE = GETDATE()
    WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID

    UPDATE dbo.RPT_DELINQUENCY_TEST
    SET STATUS_FLAG = 'B'
    WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID

    UPDATE dbo.RPT_DELINQUENCY_TEST
    SET STATUS_FLAG_INSERT_DT = GETDATE()
    WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID

    FETCH NEXT FROM DEQ INTO
    @GRGR_ID,
    @SGSG_ID,
    @SBSB_ID,
    @PASS1_GENERATION_DATE,
    @PASS1_TOTAL_AMOUNT_DUE,
    @STATUS_FLAG,
    @STATUS_FLAG_INSERT_DT
    END

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    First things first, combine your updates into a single statement
    Code:
    UPDATE dbo.RPT_DELINQUENCY_TEST 
    SET PASS1_GENERATION_DATE = GETDATE()
    , STATUS_FLAG = 'B'
    , STATUS_FLAG_INSERT_DT = GETDATE()
    WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID
    If you're getting data type conversion errors, it helps if you tell us what datatypes are being used where.
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Conversion Error

    Here are the data types:

    @GRGR_ID CHAR(10),
    @SGSG_ID CHAR(4),
    @SBSB_ID CHAR(10),
    @PASS1_GENERATION_DATE DATETIME,
    @PASS1_TOTAL_AMOUNT_DUE MONEY,
    @STATUS_FLAG CHAR(1),
    @STATUS_FLAG_INSERT_DT DATETIME

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    My guess is that the table use char for datetime
    And the fetch fail converting that to datatime

  5. #5
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Conversion Error

    Here is the table format:

    CREATE TABLE dbo.RPT_DELINQUENCY_TEST
    (
    GRGR_ID CHAR(10) NULL,
    SBSB_ID CHAR(10) NULL,
    INVOICE_DUE_DATE DATETIME NULL,
    SGSG_ID CHAR(4) NULL,
    BLEI_CK INT NULL,
    INVOICE_TOTAL_AMOUNT_DUE MONEY NULL,
    INVOICE_NUMBER CHAR(12) NULL,
    INVOICE_GEN_DATE DATETIME NULL,
    PLAN_ID CHAR(8) NULL,
    PLAN_DESCRIPTION CHAR(70) NULL,
    PASS1_GENERATION_DATE DATETIME NULL,
    PASS1_TOTAL_AMOUNT_DUE MONEY NULL,
    PASS1_END_OF_GRACE DATETIME NULL,
    STATUS_FLAG CHAR(1) NULL,
    STATUS_FLAG_INSERT_DT DATETIME NULL
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •