存储过程中字符串日期怎么比较？

Question

大家好：我建了个存储过程，如下所示：

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

Answer 1

你的参数是字符类型的, 不需要转换, 当字符串拼进去就行了

SET @SQL=@SQL+' AND EVENTS.TIME >='' '+@START_TIME + ''''

Answer 2

一般动态 T-SQL, 推荐直接使用参数传递, 而不是硬拼值. 类似下面, 这样可以防止T-SQL注入, 也可以避免因为类型转换导致的一些麻烦

DECLARE @sql nvarchar(4000);

SET @sql = 'select * from tb where id = @id';

exec sp_executesql @sql, N'@id int', @id

Answer 3

谢谢，可以了，原来要多加对引号变成字符，不然会出错，我以前是这样写的SET @SQL=@SQL+' AND EVENTS.TIME >=’+@START_TIME  ，这样的话它就会去自动转换成int

thank you very much.

---

liuwh