管理员
|
楼主#
更多
发布于：2012-12-08 13:43
 | |  |  |
日期范围查询实用函数——fn_ltDate 根据日期范围来查询是很常见的操作。表单中输入 2012-05-01 到 2012-10-01， 很容易漏掉 2012-10-01 这一天的记录，而且总要判断 is null 或者空串等也比较麻烦， 故写了这段函数。 www.atcpu.com [sql] 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
| |  | |  |
|
