对于字符串向smalldatetime转换失败问题的解决

此类问题一般出现于动态存储过程，我以up_selectTradeInfo为例简单说明该问题的解决方法 

--建表

--银行卡表

GO

USE BankCardManageDB

GO

IF EXISTS (SELECT * FROM sys.objects WHERE NAME='BankCard')

DROP TABLE BankCard

CREATE TABLE BankCard  

(

ID int identity(1,1),

CardID char(10) PRIMARY KEY,

BankName varchar(20) NOT NULL,

UserName varchar(20) NOT NULL,

Deposit money NOT NULL DEFAULT(0),

Payout money NOT NULL DEFAULT(0)

)

GO

--交易记录表

USE BankCardManageDB

IF EXISTS(SELECT * FROM sys.objects WHERE NAME='Trade')

DROP TABLE Trade

CREATE TABLE Trade  

(

TradeID BIGINT PRIMARY KEY IDENTITY(1,1),

CardID CHAR(10) FOREIGN KEY REFERENCES BankCard(CardID) NOT NULL,

TradeDate DATETIME NOT NULL,

TradeMoney MONEY CHECK(TradeMoney>0),

TradeType char(8) CHECK(TradeType='存钱' or TradeType='取钱')

)

GO

--插入测试数据

INSERT INTO BankCard VALUES('123456766','工商银行','田国兴',2000,200)

INSERT INTO Trade VALUES('123456766','20080921',30000,'存钱')

--创建存储过程 up_selectTradeInfo 查询时间段内卡的交易信息

USE BankCardManageDB

GO

IF EXISTS(SELECT * FROM sys.objects WHERE name='up_selectTradeInfo')

DROP PROCEDURE up_selectTradeInfo

GO

CREATE PROCEDURE up_selectTradeInfo

@cardID varchar(10),

@startDate smalldatetime,--时间上限

@endDate smalldatetime --时间下限

AS 

BEGIN

DECLARE @sqlStr varchar(300)

SET @sqlStr='SELECT * FROM Trade WHERE CardID='+str(@cardID)

+' AND TradeDate BETWEEN '+@startDate+' AND '+@endDate

EXEC(@sqlStr)

END

exec up_selectTradeInfo '123456766','20080920','20080921'

在执行存储过程时，发生以下错误

消息 295，级别 16，状态 3，过程 up_selectTradeInfo，第 11 行

将字符串转换为 smalldatetime 数据类型时失败。

具体解决办法如下：

把存储过程里面的SET @sqlStr一句改为（嵌套转换）

SET @sqlStr='SELECT * FROM Trade WHERE CardID='+str(@cardID)

+' AND TradeDate BETWEEN CONVERT(SMALLDATETIME,'+''''+CONVERT(VARCHAR,@startDate,112)+''''+',120)'

+' AND CONVERT(SMALLDATETIME,'+''''+CONVERT(VARCHAR,@endDate,112)+''''+',120)'

(注意：在数据类型为datetime时最好不要使用那个BETWEEN)

再执行

exec up_selectTradeInfo '123456766','20080920','20080921'

返回

TradeID              CardID     TradeDate               TradeMoney            TradeType

-------------------- ---------- ----------------------- --------------------- ---------

18                   123456766  2008-09-21 00:00:00     30000.00              存钱    

(1 行受影响)

问题解决！！