“将数据类型varchar转换为datetime时出错”

时间:2022-08-18 16:36:57

How can I solve the error in the following procedure?

如何在以下过程中解决错误?

CREATE PROCEDURE cards
    @salesman VARCHAR(10),
    @RCV10 INT,
    @RCV09 INT,
    @RCV15 INT,
    @GPRS15 INT,
    @RCV20 INT,
    @RCV25FTT INT,
    @RCV25 INT,
    @RCV31 INT,
    @RCV30 INT,
    @RCV35 INT,
    @RCV50 INT,
    @RCV55 INT,
    @SIM INT,
    @VTOPSIM INT,
    @VTOPBAL INT,
    @THREEGSIM INT,
    @entrydate DATETIME
AS
BEGIN
    IF EXISTS(
           SELECT *
           FROM   CardsIssued
           WHERE  salesman = @salesman
                  AND RCV10 > @RCV10
                  AND RCV09 > @RCV09
                  AND RCV15 > @RCV15
                  AND GPRS15 > @GPRS15
                  AND RCV20 > @RCV20
                  AND RCV25FTT > @RCV25FTT
                  AND RCV25 > @RCV25
                  AND RCV31 > @RCV31
                  AND RCV30 > @RCV30
                  AND RCV35 > @RCV35
                  AND RCV50 > @RCV50
                  AND RCV55 > @RCV55
                  AND SIM > @SIM
                  AND VtopSim > @VTOPSIM
                  AND VtopBal > @VTOPBAL
                  AND ThreeGSim > @THREEGSIM
                  AND EntryDate = @entrydate
       )
    BEGIN
        INSERT Cards_Returned
        VALUES
          (
            @salesman,
            @RCV10,
            @RCV09,
            @RCV15,
            @GPRS15,
            @RCV20,
            @RCV25FTT,
            @RCV25,
            @RCV31,
            @RCV30,
            @RCV35,
            @RCV50,
            @RCV55,
            @SIM,
            @VTOPSIM,
            @VTOPBAL,
            @THREEGSIM,
            @EntryDate
          )
    END
    ELSE
        PRINT'CARDS RETURNED CANNOT BE GREATER THAN CARDS ISSUED'
END

Run as:

运行方式:

execute cards 'S001',50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,'28/1/2013'

gives the following error:

给出以下错误:

Msg 8114, Level 16, State 5, Procedure cards, Line 0
Error converting data type varchar to datetime

5 个解决方案

#1


15  

'28/1/2013' is an ambiguous format - SQL Server might interpret it as dd/mm/yyyy or as mm/dd/yyyy. In this case, it's done the latter, and doesn't know what the 28th month of the year is.

'28 / 1/2013'是一种模糊的格式 - SQL Server可能会将其解释为dd / mm / yyyy或mm / dd / yyyy。在这种情况下,它完成后者,并不知道一年中的第28个月是什么。

Use '20130128' instead. This is always interpreted as yyyymmdd.

请改用“20130128”。这总是被解释为yyyymmdd。

#2


2  

perhaps try changing the input variable for @EntryDate to a varchar. Then, when using it further down, perform a CONVERT(datetime,@EntryDate) on it.

也许尝试将@EntryDate的输入变量更改为varchar。然后,当进一步使用它时,在其上执行CONVERT(datetime,@ EntryDate)。

#3


1  

As per your error message, problem is with the date format. Format your date to ISO format (yyyymmdd) before execution as below.

根据您的错误消息,问题是日期格式。在执行之前将日期格式化为ISO格式(yyyymmdd),如下所示。

execute cards 'S001',50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,'20130128'

#4


1  

Had the same error. In my case it was passing datetime2 value to a datetime column:

有同样的错误。在我的例子中,它将datetime2值传递给datetime列:

Had to change string to datetime (3 digits) instead of 7 digits for milliseconds:

必须将字符串更改为日期时间(3位数)而不是7位数字,以毫秒为单位:

exec [dbo].[UpdateProc] @LastViewDate='2016-07-27 11:03:04.1378957'

To:

至:

exec [dbo].[UpdateProc] @LastViewDate='2016-07-27 11:03:04.137'

#5


0  

That would be datetime conversion 103 (dd/mm/yyyy):

那将是datetime转换103(dd / mm / yyyy):

--receive parameter as varchar <br/>
declare @dateEntry as varchar(10) <br/>
set @dateEntry= '28/1/2013' 

--do the conversion <br/>
declare @dateResult as datetime <br/>
set @dateResult = convert(datetime, @dateEntry, 103) <br/>
select @dateResult <br/>

Ref: http://www.sqlusa.com/bestpractices/datetimeconversion/

参考:http://www.sqlusa.com/bestpractices/datetimeconversion/

#1


15  

'28/1/2013' is an ambiguous format - SQL Server might interpret it as dd/mm/yyyy or as mm/dd/yyyy. In this case, it's done the latter, and doesn't know what the 28th month of the year is.

'28 / 1/2013'是一种模糊的格式 - SQL Server可能会将其解释为dd / mm / yyyy或mm / dd / yyyy。在这种情况下,它完成后者,并不知道一年中的第28个月是什么。

Use '20130128' instead. This is always interpreted as yyyymmdd.

请改用“20130128”。这总是被解释为yyyymmdd。

#2


2  

perhaps try changing the input variable for @EntryDate to a varchar. Then, when using it further down, perform a CONVERT(datetime,@EntryDate) on it.

也许尝试将@EntryDate的输入变量更改为varchar。然后,当进一步使用它时,在其上执行CONVERT(datetime,@ EntryDate)。

#3


1  

As per your error message, problem is with the date format. Format your date to ISO format (yyyymmdd) before execution as below.

根据您的错误消息,问题是日期格式。在执行之前将日期格式化为ISO格式(yyyymmdd),如下所示。

execute cards 'S001',50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,'20130128'

#4


1  

Had the same error. In my case it was passing datetime2 value to a datetime column:

有同样的错误。在我的例子中,它将datetime2值传递给datetime列:

Had to change string to datetime (3 digits) instead of 7 digits for milliseconds:

必须将字符串更改为日期时间(3位数)而不是7位数字,以毫秒为单位:

exec [dbo].[UpdateProc] @LastViewDate='2016-07-27 11:03:04.1378957'

To:

至:

exec [dbo].[UpdateProc] @LastViewDate='2016-07-27 11:03:04.137'

#5


0  

That would be datetime conversion 103 (dd/mm/yyyy):

那将是datetime转换103(dd / mm / yyyy):

--receive parameter as varchar <br/>
declare @dateEntry as varchar(10) <br/>
set @dateEntry= '28/1/2013' 

--do the conversion <br/>
declare @dateResult as datetime <br/>
set @dateResult = convert(datetime, @dateEntry, 103) <br/>
select @dateResult <br/>

Ref: http://www.sqlusa.com/bestpractices/datetimeconversion/

参考:http://www.sqlusa.com/bestpractices/datetimeconversion/