日期:2014-05-17 浏览次数:20576 次
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 FUNCTION dbo.fn_ltDate
(
@startDate VARCHAR(10),
@endDate VARCHAR(10)
)
RETURNS BIT
AS
BEGIN
DECLARE @result BIT
SET @result = 0
IF (
LTRIM(RTRIM(ISNULL(@startDate, ''))) = ''
OR LTRIM(RTRIM(ISNULL(@endDate, ''))) = ''
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' UNION
SELECT 3,
'2012-4-2' UNION
SELECT 4,
'2012-5-2' UNION
SELECT 5,
'2012-6-2'
DECLARE @StartDate1 DATETIME
DECLARE @EndDate1 DATETIME
SET @StartDate1 = '2012-3-2'
SET @EndDate1 =''--为NULL就可以, 为''就不行
SELECT *
FROM @TempTalbe t
WHERE dbo.fn_ltDate(@StartDate1, CreateTime)=1
AND
dbo.fn_ltDate(CreateTime, @EndDate1) = 1
LTRIM(RTRIM(ISNULL(@startDate, '1900-01-01 00:00:00.000'))) = '1900-01-01 00:00:00.000'
OR LTRIM(RTRIM(ISNULL(@endDate, '1900-01-01 00:00:00.000'))) = '1900-01-01 00:00:00.000'
OR DATEDIFF(DAY, @startDate, @endDate) >= 0
SELECT CAST('' AS DATETIME)
/*
1900-01-01 00:00:00.000
(1 行受影响)
*/
LTRIM(RTRIM(ISNULL(@startDate, ''))) = '1900-1-1'
OR LTRIM(RTRIM(ISNULL(@endDate, ''))) = '1900-1-1'
&nbs