We have a Stored Procedure in SQL Server which expects datetime column to be in MMddyyyy format and results in error
Conversion failed when converting date and/or time from character string.
whenever the system calendar setting is set to ddMMyyyy format. The same SP works fine if other date format is set for the system such as dd-MMM-yyyy works fine and returns no error. SP throws error when the calender setting is changed to ddMMyyyy format. It will work fine for dates below 12 as it takes the first digits as month but fails when higher dates like 15 or 28 is given. I am trying to find a solution so that it will work for all calendar settings as I cannot restrict the end users from changing their calendar settings. Please help me ... Here is my SP
CREATE PROCEDURE [dbo].[usp_704000]
-- Add the parameters for the stored procedure here
@Dt uttTrnBRS readonly,
@Dt1 uttTrnBRS1 readonly
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @RowStatus Int
Declare @TrnId Int
Declare @DocNo Varchar(15)
Declare @NextNo Int
Declare @Return SmallInt
Select @RowStatus = MAX(RowStatus), @Return = 0, @TrnId = 0
From @Dt T0
BEGIN TRANSACTION
--NEW Mode Handle the Insertion of New Records
IF (@RowStatus = 2)
BEGIN
BEGIN TRY
Insert into TrnBRS (CmpId, FinYear, FYPrd, DocId, SeriesId, DocDate, DocRef, DocStatus, BankCode, BankName, BankAcNo, AcId, AcCode,
AcDesc, BankAcId, EndDt, EndBalAmt, LastRcnDt, BalAmt, Diff, Remarks, BranchCode, BaseId, CUserId, CreateDt)
Select T0.CmpId, T2.FinYear, T2.FYPrdId FYPrd, T3.DocId, T0.SeriesId, Cast(T0.DocDate as Date) DocDate, T0.DocRef, '3' DocStatus,
T0.BankCode, T0.BankName, T0.BankAcNo, T0.AcId, T0.AcCode, T0.AcDesc, T0.BankAcId,
Cast(T0.EndDt as Date) EndDt, ISNULL(T0.EndBalAmt, 0) EndBalAmt, Cast(T0.LastRcnDt as Date) LastRcnDt, ISNULL(T0.BalAmt, 0) BalAmt, ISNULL(T0.Diff, 0) Diff,
T0.Remarks, T3.BranchCode, T3.BaseId, T0.CUserId, GetDate() CreateDt
From @Dt T0 Cross Join CmpMst T1
Left Join FYPrdMst T2 ON T0.DocDate Between T2.PrdFrom and T2.PrdTo
Inner Join (Select T0.DocId, T0.BaseId, T1.SeriesId, T1.SFinYear, T1.EFinYear, T0.BranchCode
From DocNum T0 Inner Join DocNum1 T1 ON T0.DocId = T1.DocId
Where T0.BaseId = 34) T3 ON T2.FinYear Between T3.SFinYear and ISNULL(T3.EFinYear, 3000)
Where T0.RowStatus = 2;
IF (@@ERROR = 0)
SET @TrnId = @@IDENTITY;
IF (@@ERROR = 0)
Insert into TrnBRS1 (TrnId, LineNum, BranchCode, PDocNo, PDocDate, PDocRef, PRefDate, DrAmtLC, CrAmtLC, Narration, ClearAmt, BankDt, PTrnId, PBaseId, PDocId,
JnlBaseId, JnlDocId, JnlDocNo, JnlTrnId, JnlLineNum, AdjEntry, Notes, RowIndex)
Select @TrnId TrnId, ROW_NUMBER() OVER (Order by T0.RowIndex) LineNum, T0.BranchCode, T0.PDocNo, Cast(T0.PDocDate as Date) PDocDate, T0.PDocRef,
Cast(T0.PRefDate as Date) PRefDate, T0.DrAmtLC, T0.CrAmtLC, T0.Narration, T0.ClearAmt, Cast(T0.BankDt as Date) BankDt, T0.PTrnId, T0.PBaseId, T0.PDocId,
T0.JnlBaseId, T0.JnlDocId, T0.JnlDocNo, T0.JnlTrnId, T0.JnlLineNum, T0.AdjEntry, T0.Notes, ROW_NUMBER() OVER (Order by T0.RowIndex) RowIndex
From @Dt1 T0
Where ISNULL(T0.IsCheck, 'N') = 'Y'; -- and ISDATE(CONVERT(Varchar(10), T0.BankDt, 111)) = 1;
IF (@@ERROR = 0)
Insert into TrnBRS2 (TrnId, LineNum, BranchCode, PDocNo, PDocDate, PDocRef, PRefDate, DrAmtLC, CrAmtLC, Narration, PTrnId, PBaseId, PDocId,
JnlBaseId, JnlDocId, JnlDocNo, JnlTrnId, JnlLineNum, Notes, RowIndex)
Select @TrnId TrnId, ROW_NUMBER() OVER (Order by T0.RowIndex) LineNum, T0.BranchCode, T0.PDocNo, T0.PDocDate, T0.PDocRef,
T0.PRefDate, T0.DrAmtLC, T0.CrAmtLC, T0.Narration, T0.PTrnId, T0.PBaseId, T0.PDocId,
T0.JnlBaseId, T0.JnlDocId, T0.JnlDocNo, T0.JnlTrnId, T0.JnlLineNum, T0.Notes, ROW_NUMBER() OVER (Order by T0.RowIndex) RowIndex
From @Dt1 T0
Where ISNULL(T0.IsCheck, 'N') = 'N'; -- and ISDATE(CONVERT(Varchar(10), T0.BankDt, 111)) = 1;
IF (@@ERROR = 0)
Update TrnJnl1
SET RecnDt = T1.BankDt,
RecnSys = 'Y', RecnTrnId = @TrnId, RecnType = 1
From TrnJnl1 T0 Inner Join TrnBRS1 T1 ON T0.TrnId = T1.JnlTrnId and T0.LineNum = T1.JnlLineNum and T1.TrnId = @TrnId;
IF (@@ERROR = 0)
Select @NextNo = T1.NextNo 1,
@DocNo = ISNULL(T1.Prefix, '') REPLICATE('0', T1.NumLen-LEN(CAST(@NextNo as VARCHAR(5)))) CAST(@NextNo as VARCHAR(5))
ISNULL(T1.Suffix, '')
From TrnBRS T0 Inner Join DocNum1 T1 ON T0.DocId = T1.DocId and T0.SeriesId = T1.SeriesId
Where T0.TrnId = @TrnId
IF (@@ERROR = 0)
Insert into BankMst3 (BankAcId, BRDate, AcId, BRAmtLC, CUserId, CreateDate)
Select T0.BankAcId, T0.EndDt, T0.AcId, T0.EndBalAmt, T0.CUserId, GetDate() CreateDate
From TrnBRS T0
Where T0.TrnId = @TrnId;
IF (@@ERROR = 0)
Update DocNum1
SET NextNo = @NextNo
From DocNum1 T0 Inner Join TrnBRS T1 ON T0.DocID = T1.DocID and T0.SeriesId = T1.SeriesId and T1.TrnId = @TrnId
IF (@@ERROR = 0)
Update TrnBRS SET DocNo = @DocNo Where TrnId = @TrnId
IF (@@ERROR = 0)
BEGIN
COMMIT TRANSACTION;
Select 0 RetVal, @TrnId TrnId, 'Transaction Saved...' ErrMsg, 0 ErrType;
RETURN
END
END TRY
BEGIN CATCH
BEGIN
ROLLBACK TRANSACTION;
SELECT -1 RetVal, @TrnId TrnId, ERROR_MESSAGE() AS ErrMsg, 1 ErrType
RETURN
END
END CATCH
END
ELSE
BEGIN
BEGIN TRY
Insert into TrnBRS (CmpId, FinYear, FYPrd, DocId, SeriesId, DocDate, DocRef, DocStatus, CancelState, CanclTrnId, EndDt, EndBalAmt,
LastRcnDt, BalAmt, Diff, Remarks, BaseId, CUserId, CreateDt)
Select T0.CmpId, T0.FinYear, T0.FYPrd, T0.DocId, T0.SeriesId, T0.DocDate, T0.DocRef, '3' DocStatus, 'N' CancelState, T0.TrnId CanclTrnId,
T0.EndDt, ISNULL(T0.EndBalAmt, 0) EndBalAmt, T0.LastRcnDt, ISNULL(T0.BalAmt, 0) BalAmt, ISNULL(T0.Diff, 0) Diff,
T0.Remarks, T0.BaseId, T0.CUserId, GetDate() CreateDt
From TrnBRS T0 Inner Join @Dt T1 ON T0.TrnId = T1.TrnId
Where T1.RowStatus = 3;
SET @TrnId = @@IDENTITY;
Update TrnBRS
SET CancelState = 'Y', DocStatus = '4', CanclTrnId = @TrnId
From TrnBRS T0 Inner Join @Dt T1 ON T0.TrnId = T1.TrnId and T1.RowStatus = 3;
Insert into TrnBRS1 (TrnId, LineNum, BranchCode, PDocNo, PDocDate, PDocRef, PRefDate, DrAmtLC, CrAmtLC, Narration, ClearAmt,
BankDt, PTrnId, PBaseId, PDocId, JnlBaseId, JnlDocId, JnlDocNo, JnlTrnId, JnlLineNum, AdjEntry, Notes, RowIndex)
Select @TrnId TrnId, T0.LineNum, T0.BranchCode, T0.PDocNo, T0.PDocDate, T0.PDocRef, T0.PRefDate, T0.DrAmtLC, T0.CrAmtLC,
T0.Narration, T0.ClearAmt *-1, T0.BankDt, T0.PTrnId, T0.PBaseId, T0.PDocId, T0.JnlBaseId, T0.JnlDocId, T0.JnlDocNo,
T0.JnlTrnId, T0.JnlLineNum, T0.AdjEntry, T0.Notes, T0.RowIndex
From TrnBRS1 T0 Inner Join @Dt T1 ON T0.TrnId = T1.TrnId and T1.RowStatus = 3;
Update TrnJnl1
SET RecnDt = NULL, RecnSys = NULL, RecnTrnId = NULL, RecnType = NULL
From TrnJnl1 T0 Inner Join TrnBRS1 T1 ON T0.TrnId = T1.JnlTrnId and T0.LineNum = T1.JnlLineNum and T1.TrnId = @TrnId
Select @NextNo = T1.NextNo 1,
@DocNo = ISNULL(T1.Prefix, '')
Substring(Cast(Year(T0.DocDate) as VARCHAR(4)),3,2) REPLICATE('0', T1.NumLen-LEN(CAST(@NextNo as VARCHAR(5)))) CAST(@NextNo as VARCHAR(5))
ISNULL(T1.Suffix, '')
From @Dt T0 Inner Join DocNum1 T1 ON T0.DocId = T1.DocId
Update DocNum1
SET NextNo = @NextNo
From DocNum1 T0 Inner Join @Dt T1 ON T0.DocID = T1.DocID
IF (@@ERROR <> 0) RETURN @@ERROR
Update TrnBRS SET DocNo = @DocNo Where TrnId = @TrnId
IF (@@ERROR <> 0) RETURN @@ERROR
BEGIN
COMMIT TRANSACTION;
Select 0 RetVal, @TrnId TrnId, 'Transaction Reversed...' ErrMsg, 0 ErrType;
RETURN
END
END TRY
BEGIN CATCH
BEGIN
ROLLBACK TRANSACTION
SELECT -1 RetVal, @TrnId TrnId, ERROR_MESSAGE() AS ErrMsg, 1 ErrType
RETURN
END
END CATCH
END
END
I had tried to convert the ddMMyyyy format(103) using the convert statement SELECT CONVERT(DATE, T1.BankDt, 103) instead of T1.BankDt(which is the datetime field we are trying to manipulate) but didn't succeed. It still throws error saying conversion failed for dates above 12 as it expects dates in MMddyyyy format
EDIT 2
Thank You all for the supports... I had find the issue at last, the real issue was in another stored procedure which is used to populate the initial value in which the BankDt field was populated with '' empty string so that the field is treated as character string instead of date field. I had changed '' BankDt to Convert(Date,null,103) BankDt ... Now everything works fine
CodePudding user response：
the real issue was in another stored procedure which is used to populate the initial value in which the BankDt field was populated with '' empty string so that the field is treated as character string instead of date field. I had changed
'' BankDt
to
Convert(Date,null,103) BankDt
... Now everything works fine