日期范围查询实用函数—— fn_ltDate
根据日期范围来查询是很常见的操作。表单中输入 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_ltDateENDELSEBEGIN PRINT '不存在，新建'ENDGO --Create by: yenange--Description: 前日期是否<=后面的日期(在同一天也算是小于等于)--Parameters : @startDate 前日期，@endDate 后日期.-- 两参数传入时的类型可为 varchar 或者 datetime 型-- 若有一参数为null或者'', 则返回1 (也算是小于等于)--Return : 是 1 否 0CREATE FUNCTION dbo.fn_ltDate( @startDate VARCHAR(32), @endDate VARCHAR(32))RETURNS BITASBEGIN 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 @resultENDGO DECLARE @TempTalbe TABLE (Id INT, CreateTime DATETIME)INSERT INTO @TempTalbeSELECT 1,'2012-1-2' UNIONSELECT 2,'2012-3-2 00:00:00' UNIONSELECT 3,'2012-4-2' UNIONSELECT 4,'2012-5-2 23:59:59' UNIONSELECT 5,'2012-6-2 23:59:59' --参数为 datetime 型DECLARE @StartDate1 DATETIMEDECLARE @EndDate1 DATETIMESET @StartDate1 = '2012-3-2'SET @EndDate1 = '' SELECT *FROM @TempTalbe tWHERE 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 tWHERE dbo.fn_ltDate(@StartDate1, CreateTime) = 1 AND dbo.fn_ltDate(CreateTime, @EndDate1) = 1
