Conversion failed Error SQL Date format Mismatch with C# Datatable datetime fromat depending on system calender setting

Question

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

You're showing the wrong thing here. What is the definition of `uttTrnBRS`/`uttTrnBRS1` and how are you populating them from C#? I suspect this error will happen with a stored procedure that just says `SELECT * FROM @dt;` - making this wall of code completely unnecessary. (As an aside, no, don't ever use `@@IDENTITY`, use `SCOPE_IDENTITY()`.) — Aaron Bertrand, Apr 01 '22 at 06:36
`It will work fine for dates below 12` - unlikely that works as well as you think. Just because it doesn't cause an exception doesn't mean it's behaving correctly. It isn't magic, if you say `06112022` and you meant November 6th but stored June 11th, I guess that "worked fine" but did it really? The real fix is going to be avoiding any kind of regional format at all. Why are you passing dates as strings in any format that depends on regional settings? Why not use `yyyyMMdd` so there is no chance of misinterpretation? Or a proper date type in C# to begin with? — Aaron Bertrand, Apr 01 '22 at 06:39
Basically, the problem doesn't involve SQL Server at all. You need to fix your C# code to not use date strings that rely on end users' settings. — Aaron Bertrand, Apr 01 '22 at 06:43
A date/datetime column in any database has no format. If you pass the value from a string than the database will have to convert it, in that case you can either go the hard way and send in a format the database might understand, or go the easy way and send in a format that is region independent, like yyyymmdd for example. The problem has nothing to do with your database, but with your code that formats the value before it sends it to the database. You don't blame the database for giving an error if you send ABC to an int column either, don't you — GuidoG, Apr 01 '22 at 07:39
thank you all for your kind responses..@aaron-bertrand Sorry for not including uttTrnBRS/uttTrnBRS1 as I cannot insert the whole definition here...we used datetime2 for date fields in both uttTrnBRS/uttTrnBRS1 and the dates in c# are populated with dateedit editor in a grid control which is formatted to display dates in ddMMyyyy format.(the end users use the date format ddMMyyyy mostly) also the data type used in C# datatable is datetime... — E_VAT_BIS_SOL Davean, Apr 01 '22 at 08:23
Thanks for your kind response @GuidoG, ... We pass a datatable with a column of type datetime not any string type. we are wondering how we can format the date values in datatable in c# which always depends on the system calender date format — E_VAT_BIS_SOL Davean, Apr 01 '22 at 08:30
You can't, a datetime column has no format. So if you get this error than somewhere there must be a string or varchar involved — GuidoG, Apr 01 '22 at 08:36
`Select @RowStatus = MAX(RowStatus), @Return = 0, @TrnId = 0 From @Dt T0` So you expect your UDTT to have multiple rows but you don't properly handle them in your code. Bad habits, bad assumptions. And what happens when an error occurs in a TRY block? Do you need to check `IF (@@ERROR = 0)` after each statement? I could go on - but a thorough review of this code by a knowledgeable person is needed. — SMor, Apr 01 '22 at 11:10

score 0 · Accepted Answer · answered Apr 04 '22 at 06:10

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

Conversion failed Error SQL Date format Mismatch with C# Datatable datetime fromat depending on system calender setting

1 Answers1