34,587
社区成员
发帖
与我相关
我的任务
分享
Select * from A where 日期1>='2011-10-01' and 日期1<='2011-11-11' and 日期3>='2011-11-01' and 日期3<='2011-11-11
create procedure ProName
@begindate1 datetime,
@enddate1 datetime,
@begindate2 datetime,
@enddate2 datetime,
@begindate3 datetime,
@enddate3 datetime
as
Select * from A
where 日期1>=isnull(@begindate1,日期1)
and 日期1<=isnull(@enddate1,日期1)
and 日期1>=isnull(@begindate2,日期1)
and 日期1<=isnull(@enddate2,日期1)
and 日期1>=isnull(@begindate3,日期1)
and 日期1<=isnull(@enddate3,日期1)
select *
from tb
where date1 between (case when @d1 is not null then convert(datetime,substring(@d1,1,charindex(',',@d1)-1)) else date1 end)
and (case when @d1 is not null then convert(datetime,substring(@d1,charindex(',',@d1)+1,len(@d1)-charindex(',',@d1))) else date1 end)
and date2 between (case when @d2 is not null then convert(datetime,substring(@d2,1,charindex(',',@d2)-1)) else date1 end)
and (case when @d2 is not null then convert(datetime,substring(@d2,charindex(',',@d2)+1,len(@d2)-charindex(',',@d2))) else date1 end)
and date3 between (case when @d3 is not null then convert(datetime,substring(@d3,1,charindex(',',@d3)-1)) else date1 end)
and (case when @d3 is not null then convert(datetime,substring(@d3,charindex(',',@d3)+1,len(@d3)-charindex(',',@d3))) else date1 end)
CREATE PROC PR_TEST
@SD1 DATETIME = NULL
,@ED1 DATETIME = NULL
,@SD2 DATETIME = NULL
,@ED2 DATETIME = NULL
,@SD3 DATETIME = NULL
,@ED3 DATETIME = NULL
AS
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'Select * from A where 1= 1'
IF @SD1 IS NOT NULL AND @ED1 IS NOT NULL
SET @SQL =@SQL + ' AND 日期1>=''' + CONVERT(VARCHAR(10,@SD1,120) + '''
and 日期1<=''' + CONVERT(VARCHAR(10,@ED1,120) + ''' '
IF @SD2 IS NOT NULL AND @ED2 IS NOT NULL
SET @SQL =@SQL + ' AND 日期2>=''' + CONVERT(VARCHAR(10,@SD2,120) + '''
and 日期2<=''' + CONVERT(VARCHAR(10,@ED2,120) + ''' '
IF @SD3 IS NOT NULL AND @ED3 IS NOT NULL
SET @SQL =@SQL + ' AND 日期3>=''' + CONVERT(VARCHAR(10,@SD3,120) + '''
and 日期3<=''' + CONVERT(VARCHAR(10,@ED3,120) + ''' '
EXEC(@SQL)
GO
CREATE PROC PR_TEST
@SD1 DATETIME = NULL
,@ED1 DATETIME = NULL
,@SD2 DATETIME = NULL
,@ED2 DATETIME = NULL
,@SD3 DATETIME = NULL
,@ED3 DATETIME = NULL
AS
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'Select * from A where 1= 1'
IF @SD1 IS NOT NULL AND @ED1 IS NOT NULL
SET @SQL =@SQL + ' AND 日期1>=''' + CONVERT(VARCHAR(10,@SD1,120) + '''
and 日期1<=''' + CONVERT(VARCHAR(10,@ED1,120) + '''
IF @SD2 IS NOT NULL AND @ED2 IS NOT NULL
SET @SQL =@SQL + ' AND 日期2>=''' + CONVERT(VARCHAR(10,@SD2,120) + '''
and 日期2<=''' + CONVERT(VARCHAR(10,@ED2,120) + '''
IF @SD3 IS NOT NULL AND @ED3 IS NOT NULL
SET @SQL =@SQL + ' AND 日期3>=''' + CONVERT(VARCHAR(10,@SD3,120) + '''
and 日期3<=''' + CONVERT(VARCHAR(10,@ED3,120) + '''
EXEC(@SQL)
GO