22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE(ID int,Name varchar(10),Birthday varchar(10))
INSERT @t SELECT 1,'aa','1999-01-01'
UNION ALL SELECT 2,'bb','1996-02-29'
UNION ALL SELECT 3,'bb','1934-03-01'
UNION ALL SELECT 4,'bb','1966-04-01'
UNION ALL SELECT 5,'bb','1997-05-01'
UNION ALL SELECT 6,'bb','1922-11-21'
UNION ALL SELECT 7,'bb','1989-12-11'
DECLARE @dt1 varchar(10),@dt2 varchar(10)
--下面赋值这样查询无问题
set @dt1='2000-02-05'
set @dt2='2003-11-28'
--但是如果赋值一个变量就无效了
set @dt1=:dt1 --dt1是人工选择的一个日期,是2000-1-5这种格式
set@dt2=:dt2 --dt2也是人工选择一个日期
SELECT * FROM @t
WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
BETWEEN @dt1 AND @dt2
and DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
BETWEEN @dt1 AND @dt2
IF @dt1 <= @dt2
BEGIN
SELECT * FROM @t
WHERE RIGHT(Birthday,5) BETWEEN @dt1 AND @dt2
END
ELSE -- 考虑跨年底的情况
BEGIN
SELECT * FROM @t
WHERE @dt1 <= RIGHT(Birthday,5)
OR RIGHT(Birthday,5) <= @dt2
END
--将输入的 2000-1-5 格式统一成 01-05 格式
set @dt1 = Right(Convert(varchar(10), Convert(datetime,:dt1,120), 120), 5)
set @dt2 = Right(Convert(varchar(10), Convert(datetime,:dt2,120), 120), 5)
-- 不看年份直接比较月日不就成了
SELECT * FROM @t
WHERE RIGHT(Birthday,5) BETWEEN @dt1 AND @dt2
DECLARE @t TABLE(ID int,Name varchar(10),Birthday varchar(10))
INSERT @t SELECT 1,'aa','1999-01-01'
UNION ALL SELECT 2,'bb','1996-02-29'
UNION ALL SELECT 3,'bb','1934-03-01'
UNION ALL SELECT 4,'bb','1966-04-01'
UNION ALL SELECT 5,'bb','1997-05-01'
UNION ALL SELECT 6,'bb','1922-11-21'
UNION ALL SELECT 7,'bb','1989-12-11'
DECLARE @P8startDate DATETIME,@P9endDate DATETIME
--set @P8startDate=:P8startdate
--set @P9endDate=:P9enddate
SET @P8startDate='2014-12-01'
SET @P9endDate='2015-02-28'
SELECT * FROM @t WHERE
DATEADD(Year,DATEDIFF(Year,Birthday,@P8startDate),Birthday) BETWEEN @P8startDate AND @P9endDate
OR DATEADD(Year,DATEDIFF(Year,Birthday,@P9endDate),Birthday) BETWEEN @P8startDate AND @P9endDate
这个脚本的结果是,
/*
1 aa 1999-01-01
2 bb 1996-02-29
7 bb 1989-12-11
*/
如果这是你想的结果就没有错~
空值,是输入查询参数的空值还是,Birthday的空值呢?
Birthday建议储存为 DATETIME 占用空间比VARCHAR(10)小~
用OR原因是
那个条件判断就变成以下的判断
把Birthday的年数改为和@P8startDate一样的年数,或改为和@P9endDate一样的年数时,必须在P8startDate和@P9endDate的范围之间。
因此,
1 aa 1999-01-01 2014-01-01X 2015-01-01√
2 bb 1996-02-29 2014-02-28X 2015-02-28√
7 bb 1989-12-11 2014-12-11√ 2015-12-11X
这些数据是符合的,改成AND的话,就一个值也没有了~
DECLARE @P8startDate DATETIME,@P9endDate DATETIME
set @P8startDate=:P8startdate
set @P9endDate=:P9enddate VARCHAR(10)
SET @P8startDate='2014-12-01'
SET @P9endDate='2015-02-28'
SELECT * FROM #sxx WHERE
DATEADD(Year,DATEDIFF(Year,Birthday,@P8startDate),Birthday) BETWEEN @P8startDate AND @P9endDate
OR DATEADD(Year,DATEDIFF(Year,Birthday,@P9endDate),Birthday) BETWEEN @P8startDate AND @P9endDate
感觉没必要动态执行~Declare @ssql Nvarchar(4000)
Declare @P8startDate datetime
Declare @P9endDate datetime
set @P8startDate=:P8startdate
set @P9endDate=:P9enddate
Set @sSql='
select * from #sxx
where DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P8startDate as datetime)),Birthday)
BETWEEN cast(@P8startDate as datetime) AND cast(@P9endDate as datetime)
and DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P9endDate as datetime)),Birthday)
BETWEEN cast(@P8startDate as datetime) AND cast(@P9endDate as datetime)
'
exec SYS.sp_executesql @sSQL,N'@P8startDate datetime,@P9endDate datetime',@P8startDate,@P9endDate
这样试下
Declare @ssql varchar(8000)
Set @sSql='
Declare @P8startDate datetime
Declare @P9endDate datetime
set @P8startDate=:P8startdate
set @P9endDate=:P9enddate
select * from #sxx
where DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P8startDate as datetime)),Birthday)
BETWEEN cast(@P8startDate as datetime) AND cast(@P9endDate as datetime)
and DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P9endDate as datetime)),Birthday)
BETWEEN cast(@P8startDate as datetime) AND cast(@P9endDate as datetime)
'
exec(@sSQL)
试试这个。把声明变量放在里面
Declare @ssql varchar(8000)
Declare @P8startDate datetime
Declare @P9endDate datetime
set @P8startDate=:P8startdate
set @P9endDate=:P9enddate
Set @sSql='
select * from #sxx
where DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P8startDate as datetime)),Birthday)
BETWEEN cast(@P8startDate as datetime) AND cast(@P9endDate as datetime)
and DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P9endDate as datetime)),Birthday)
BETWEEN cast(@P8startDate as datetime) AND cast(@P9endDate as datetime)
'
exec(@sSQL)
当使用这种语句时,提示必须声明标量变量“@P8startDate ”