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/