• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

SQL 2000 to SQL 2012 Stored Procedure issue

Below is the Stored Procedure that worked in SQL 2000, but in SQL 2012 throws error -
"Msg 241, Level 16, State 1, Procedure usp_UpdateInteriorDC, Line 51
Conversion failed when converting date and/or time from character string."


USE [ABCDEF]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER                                       PROC [dbo].[usp_UpdateInteriorDC]
      @intInteriorTransID INTEGER ,
      @chvIssueDate VARCHAR(20),
      @chvRevDate VARCHAR(20) ,
      @chvItemDescription VARCHAR(100),
      @intManufacturerID INTEGER ,
      @chvManufacturerFOB      VARCHAR(50),
      @chvManufacturerLeadTime VARCHAR(20),
      @intFurnishedInstalledFlag INTEGER ,
      @chvSpecialNotations VARCHAR(2000),
      @intUserID INTEGER,
      @chvRevisionNotes VARCHAR(255),
      @intMainInteriorID INTEGER ,
      @chvModifiedTime VARCHAR(20),
      @chrErrCode CHAR(1) OUTPUT,
      @chvErrMsg VARCHAR(200) OUTPUT
AS
DECLARE @chvCurRevDate VARCHAR(20)
BEGIN

      IF @chvRevDate IS NOT NULL AND  @chvRevDate <> ''
            SET @chvCurRevDate = @chvRevDate

      /* Insert Trans Master details based on values passed
         and values generated */
      UPDATE ICS_Interior_Trans_Master
            SET Issue_date = @chvIssueDate,
            Rev_Date = ISNULL(@chvCurRevDate,Rev_Date),
            Item_Description = @chvItemDescription,
            Manufacturer_ID = @intManufacturerID,
            Manufacturer_FOB = @chvManufacturerFOB,
            Manufacturer_Leadtime = @chvManufacturerLeadtime,
            Furnished_Installed_Flag =@intFurnishedInstalledFlag,
            Special_Notations = @chvSpecialNotations,
            Revision_Notes =@chvRevisionNotes,
            Modified_By = @intUserId,
            Modified_Datetime = GetDate()
      WHERE
            Interior_Trans_ID = @intInteriorTransID      AND
            CONVERT(VARCHAR(20), Modified_DateTime,113) = CONVERT(VARCHAR(20), @chvModifiedTime,113)
            

      IF @@ERROR = 0
      BEGIN
            SET @chrErrCode = '0'
            SET @chvErrMsg = dbo.FNGETErrorMessage('ER014')
      END
      ELSE
      BEGIN
            SET @chrErrCode = '2'
            SET @chvErrMsg       =  dbo.FNGETErrorMessage('ER008')
            RETURN
      END

END

I've tried changing the Datetime format, but would appreciate hearing if someone can see what it's issue is????

Thank you,
0
urthrilled
Asked:
urthrilled
1 Solution
 
Ryan SmithSr. Systems EngineerCommented:
Change this:

ALTER                                       PROC [dbo].[usp_UpdateInteriorDC]

to

ALTER                                       PROCEDURE dbo.usp_UpdateInteriorDC
0
 
Scott PletcherSenior DBACommented:
Most likely the input value for @chvModifiedTime is not in a valid format.  20 characters is not enough bytes to hold the full string value of format 113, which is "dd mon yyyy hh:mi:ss.mmm".  A first quick shot would be to increase the length of @chvModifiedTime to at least 24 bytes.  The CONVERT in the SELECT will strip the ms anyway.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now