--这样查询就行啦--和查询生日的慨念相同.将所有的年份统一起来就行了.declare @d datetime set @d=getdate() --查询那一天的数据select * from 表 where dateadd(year,year(cndtStartDate)-year(@d),@d) between cndtStartDate and cndtEndDate
--下面是例子:declare @t table(id int identity(1,1),cndtStartDate datetime,cndtEndDate datetime) insert into @t(cndtStartDate,cndtEndDate) select '2001-01-02','2001-01-02' --一天完成 union all select '2001-01-01','2003-01-01' --跨年 union all select '2002-01-01','2002-02-01' --跨月 union all select '2003-02-01','2003-02-01' union all select '2003-03-01','2003-02-01' union all select '2003-04-01','2004-03-01'--定义要查询的日期 declare @d datetime set @d='2003-01-02' --查询1月2号的记录--查询 select * from @t where dateadd(year,year(cndtStartDate)-year(@d),@d) between cndtStartDate and cndtEndDate/*--测试结果 (所影响的行数为 6 行)id cndtStartDate cndtEndDate ----------- -------------------------- --------------------------- 1 2001-01-02 00:00:00.000 2001-01-02 00:00:00.000 2 2001-01-01 00:00:00.000 2003-01-01 00:00:00.000 3 2002-01-01 00:00:00.000 2002-02-01 00:00:00.000(所影响的行数为 3 行)--*/
select * from yourtable where getdate() between cndtstartdate and cndtenddate or datename(day,cndtstartdate)=datename(day,getdate())
DATEDIFF ( dd , cndtStartDate, cndtEndDate) =0
DECLARE @cndtEndDate datetime
DECLARE @Today datetime
DECLARE @StartTemp varchar(20)
DECLARE @EndTemp varchar(20)
SELECT @cndtStartDate = '2003-02-23 10:18'
SELECT @cndtEndDate = '2004-08-23 10:18'
SELECT @Today = '2003-12-24 10:18'
SELECT @StartTemp =
Convert( varchar(10), Year(@cndtStartDate) ) + '-' +
Convert( varchar(10), Month(@Today) ) + '-' +
Convert( varchar(10), Day(@Today) )
SELECT @StartTemp = Convert( datetime, @StartTemp )SELECT @EndTemp =
Convert( varchar(10), Year(@cndtEndDate) ) + '-' +
Convert( varchar(10), Month(@Today) ) + '-' +
Convert( varchar(10), Day(@Today) )
SELECT @EndTemp = Convert( datetime, @EndTemp )if( @EndTemp <> @EndTemp )
SELECT * FROM tbMemorabilia WHERE cndtStartDate <= @StartTemp AND cndtEndDate >= @StartTemp
else
SELECT * FROM tbMemorabilia WHERE cndtStartDate <= @StartTemp
set @d=getdate() --查询那一天的数据select *
from 表
where dateadd(year,year(cndtStartDate)-year(@d),@d)
between cndtStartDate and cndtEndDate
insert into @t(cndtStartDate,cndtEndDate)
select '2001-01-02','2001-01-02' --一天完成
union all select '2001-01-01','2003-01-01' --跨年
union all select '2002-01-01','2002-02-01' --跨月
union all select '2003-02-01','2003-02-01'
union all select '2003-03-01','2003-02-01'
union all select '2003-04-01','2004-03-01'--定义要查询的日期
declare @d datetime
set @d='2003-01-02' --查询1月2号的记录--查询
select *
from @t
where dateadd(year,year(cndtStartDate)-year(@d),@d)
between cndtStartDate and cndtEndDate/*--测试结果
(所影响的行数为 6 行)id cndtStartDate cndtEndDate
----------- -------------------------- ---------------------------
1 2001-01-02 00:00:00.000 2001-01-02 00:00:00.000
2 2001-01-01 00:00:00.000 2003-01-01 00:00:00.000
3 2002-01-01 00:00:00.000 2002-02-01 00:00:00.000(所影响的行数为 3 行)--*/