根据日期范围来查询是很常见的操作。表单中输入 2012-05-01 到 2012-10-01， 很容易漏掉 2012-10-01 这一天的记录， 而且总要判断 is null 或者空串等也比较麻烦， 故写了这段函数。

IF EXISTS(
       SELECT *
       FROM   dbo.sysobjects
       WHERE  id = OBJECT_ID(N'[dbo].fn_ltDate')
              AND (TYPE = 'FN' OR TYPE = 'TF' OR TYPE = 'IF')
   )
BEGIN
    PRINT '已存在，删除再新建'
    DROP FUNCTION fn_ltDate
END
ELSE
BEGIN
    PRINT '不存在，新建'
END
GO
  
--Create   by: yenange
--Description: 前日期是否<=后面的日期(在同一天也算是小于等于)
--Parameters : @startDate 前日期，@endDate 后日期.
--             两参数传入时的类型可为 varchar 或者 datetime 型
--             若有一参数为null或者'', 则返回1 (也算是小于等于)
--Return     : 是 1 否 0
CREATE FUNCTION dbo.fn_ltDate
(
    @startDate  VARCHAR(32),
    @endDate    VARCHAR(32)
)
RETURNS BIT
AS
BEGIN
    DECLARE @result BIT
    SET @result = 0
    IF (
           LTRIM(RTRIM(ISNULL(@startDate, ''))) = ''
           OR LTRIM(RTRIM(ISNULL(@endDate, ''))) = ''
           OR DATEDIFF(DAY, @startDate, '1900-01-01 00:00:00.000') = 0
           OR DATEDIFF(DAY, @endDate, '1900-01-01 00:00:00.000') = 0
           OR DATEDIFF(DAY, @startDate, @endDate) >= 0
       )
    BEGIN
        SET @result = 1
    END
     
    RETURN @result
END
GO
  
DECLARE @TempTalbe TABLE (Id INT, CreateTime DATETIME)
INSERT INTO @TempTalbe
SELECT 1,'2012-1-2' UNION
SELECT 2,'2012-3-2 00:00:00' UNION
SELECT 3,'2012-4-2' UNION
SELECT 4,'2012-5-2 23:59:59' UNION
SELECT 5,'2012-6-2 23:59:59'
 
--参数为 datetime 型
DECLARE @StartDate1 DATETIME
DECLARE @EndDate1 DATETIME
SET @StartDate1 = '2012-3-2'
SET @EndDate1 = ''
  
SELECT *
FROM   @TempTalbe t
WHERE  dbo.fn_ltDate(@StartDate1, CreateTime) = 1
       AND dbo.fn_ltDate(CreateTime, @EndDate1) = 1
 
--参数为 varchar 型
DECLARE @StartDate2 VARCHAR(10)
DECLARE @EndDate2 VARCHAR(10)
SET @StartDate1 = '2012-3-2'
SET @EndDate1 = '2012-5-2'
SELECT *
FROM   @TempTalbe t
WHERE  dbo.fn_ltDate(@StartDate1, CreateTime) = 1
       AND dbo.fn_ltDate(CreateTime, @EndDate1) = 1