小调一下
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-2-5'
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,cast(@dt1 as date)),Birthday)
BETWEEN cast(@dt1 as date) AND cast(@dt2 as DATE)
and DATEADD(Year,DATEDIFF(Year,Birthday,cast(@dt2 as DATE)),Birthday)
BETWEEN cast(@dt1 as date) AND cast(@dt2 as DATE)
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-2-5'
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,cast(@dt1 as date)),Birthday)
BETWEEN cast(@dt1 as date) AND cast(@dt2 as DATE)
and DATEADD(Year,DATEDIFF(Year,Birthday,cast(@dt2 as DATE)),Birthday)
BETWEEN cast(@dt1 as date) AND cast(@dt2 as DATE)
set @dt1='2000-02-05'
set @dt2='2003-11-28'
肯定是所有记录啊。肯定所有人在2001年都要过生日啊。(2月29日不考虑)
set@dt2=:dt2
这个赋值语法不是ORACLE里面的吗
比如,是在NET程序里执行SQL吗
这个试了 提示 必须声明标量变量 "@dt1"
这个试了 提示 必须声明标量变量 "@dt1"把你的原本的执行脚本贴上来看下
Declare @P8startDate varchar(10)
Declare @P9endDate varchar(10)
set @P8startDate=:P8startdate --这是一个参数,可手工输入日期
set @P9endDate=:P9enddate --同上
select * from #sxx
where DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P8startDate as date)),Birthday)
BETWEEN cast(@P8startDate as date) AND cast(@P9endDate as date)
and DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P9endDate as date)),Birthday)
BETWEEN cast(@P8startDate as date) AND cast(@P9endDate as date)
类型 date 不是已定义的系统类型
2005没有date类型
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 ”
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 @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
这样试下
这边要NVARCHAR
我已经帮你改过来了,你改回去?
sqlserver 根本就没有这种语法,要不你把你的查询写成存储过程,前端再调用,传参数进去。
2.DATEADD(Year,DATEDIFF(Year,Birthday,cast(@P8startDate as datetime)),Birthday)
这个不知道你的where过滤做什么,上面这个的结果不就是(@P8startDate 吗。
先这样吧~~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
感觉没必要动态执行~
中间为什么是or,感觉是and才对,or查旬结果是全部的好像
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的话,就一个值也没有了~
--将输入的 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
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
统一传入格式为 01-05;
:dt2 如果是月底可以用 02-31 表示,SQL 中直接进行字符串比较,就不会发生转换类型时 02-31 是无效日期的错误。