积极答复者
存储过程中字符串日期怎么比较?

问题
-
大家好:我建了个存储过程,如下所示:
ALTER PROCEDURE dbo.SelectEvents
@ID varchar(30)=NULL,
@CCN_ID char(2)=NULL,
@DEPT_ID char(3)=NULL,
@TYPE_ID char(2)=NULL,
@START_TIME char(10)=NULL,
@END_TIME char(10)=NULL,
@C_USER varchar(50)=NULL,
@RE int OUTPUT
AS
DECLARE @SQL varchar(1000)
SET DATEFORMAT ymd
BEGIN
IF(@START_TIME IS NULL OR @END_TIME IS NULL)
SET @SQL='SELECT TOP 1000 EVENTS.ID, EVENTS_TYPE.TYPE_NAME, EVENTS.SUB_TYPE,
COMPANY.CCN_NAME, DEPT.DEPT_NAME, EVENTS.USERS, EVENTS.PROBLEM,
EVENTS.SOLUTION, EVENTS.TIME, EVENTS.C_TIME, EVENTS.C_USER,
EVENTS.DES
FROM EVENTS INNER JOIN
EVENTS_TYPE ON EVENTS.TYPE_ID = EVENTS_TYPE.TYPE_ID INNER JOIN
COMPANY ON EVENTS.CCN_ID = COMPANY.CCN_ID INNER JOIN
DEPT ON EVENTS.DEPT_ID = DEPT.DEPT_ID '
ELSE
SET @SQL= 'SELECT EVENTS.ID, EVENTS_TYPE.TYPE_NAME, EVENTS.SUB_TYPE,
COMPANY.CCN_NAME, DEPT.DEPT_NAME, EVENTS.USERS, EVENTS.PROBLEM,
EVENTS.SOLUTION, EVENTS.TIME, EVENTS.C_TIME, EVENTS.C_USER,
EVENTS.DES
FROM EVENTS INNER JOIN
EVENTS_TYPE ON EVENTS.TYPE_ID = EVENTS_TYPE.TYPE_ID INNER JOIN
COMPANY ON EVENTS.CCN_ID = COMPANY.CCN_ID INNER JOIN
DEPT ON EVENTS.DEPT_ID = DEPT.DEPT_ID '
IF(@ID IS NOT NULL)
BEGIN
SET @SQL='SELECT EVENTS.ID, EVENTS_TYPE.TYPE_NAME, EVENTS.SUB_TYPE,
COMPANY.CCN_NAME, DEPT.DEPT_NAME, EVENTS.USERS, EVENTS.PROBLEM,
EVENTS.SOLUTION, EVENTS.TIME, EVENTS.C_TIME, EVENTS.C_USER,
EVENTS.DES
FROM EVENTS INNER JOIN
EVENTS_TYPE ON EVENTS.TYPE_ID = EVENTS_TYPE.TYPE_ID INNER JOIN
COMPANY ON EVENTS.CCN_ID = COMPANY.CCN_ID INNER JOIN
DEPT ON EVENTS.DEPT_ID = DEPT.DEPT_ID WHERE EVENTS.ID='+@ID
END
ELSE
BEGIN
SET @SQL=@SQL+' WHERE EVENTS.ID>0'
IF(@CCN_ID IS NOT NULL)
SET @SQL=@SQL+' AND EVENTS.CCN_ID='+@CCN_ID
IF(@DEPT_ID IS NOT NULL)
SET @SQL=@SQL+' AND EVENTS.DEPT_ID='+@DEPT_ID
IF(@TYPE_ID IS NOT NULL)
SET @SQL=@SQL+' AND EVENTS.TYPE_ID='+@TYPE_ID
IF(@C_USER IS NOT NULL)
SET @SQL=@SQL+' AND EVENTS.C_USER='+@C_USER
IF(@START_TIME IS NOT NULL AND @END_TIME IS NOT NULL)
BEGIN
SET @SQL=@SQL+' AND EVENTS.TIME >= '+convert(datetime,@START_TIME,120)
END
END
SET @SQL=@SQL+' ORDER BY EVENTS.ID DESC'
BEGIN TRAN
print @SQL
EXEC(@SQL)
IF(@@error=0)
BEGIN
COMMIT TRAN
SET @RE=1
END
ELSE
BEGIN
ROLLBACK TRAN
SET @RE=0
END
END现在问题是:这一条语句(SET @SQL=@SQL+' AND EVENTS.TIME >= '+convert(datetime,@START_TIME,120))总是出错,我不知道在sql中,字符串日期怎么比较大小,在表中,EVENTS.TIME字段是按日期格式(如2010-06-25)存储的,类型为char(10),我现在是想输入两个参数,一个开始日期,一个结束日期,来查询这个日期段的数据,请问这条语句正确的输写方式是什么,如果按上面写的话,就提示“从字符串向DateTime转换时失败”,如果直接比较SET @SQL=@SQL+' AND EVENTS.TIME>='+@START_TIME的话,提示“在将 varchar 值 '2010-06-25' 转换成数据类型 int 时失败”,谢谢!
liuwh
答案
全部回复
-
-
谢谢,可以了,原来要多加对引号变成字符,不然会出错,我以前是这样写的SET @SQL=@SQL+' AND EVENTS.TIME >=’+@START_TIME ,这样的话它就会去自动转换成int
thank you very much.
liuwh