select ... from ...where :dt between STRARTDATE and ENDDATE and STRARTDATE is not null and ENDDATE is not null
select * from tablename where getdate() >= isnull(STARTDATE,getdate()) and getdate() <= isnull(ENDDATE,getdate())
select * from tb where getdate() between startdate and enddate
select * from tablename where (getdate()>=STARTDATE and getdate()<=ENDDATE) or STARTDATE is null
select * from tablename where (getdate()>=STARTDATE and getdate()<=ENDDATE) or STARTDATE is null or ENDDATE is null
更正: 因为getdate()格式为yyyy-mm-dd hh:mm:ss.000 不能直接和你的startedate 2002-12-10 比较 ,所以要做一下转换select * from tb where convert(char(8),getdate(),112) between convert(char(8),convert(datetime,'startdate),112) and convert(char(8),convert(datetime,'endate'),112)
不好意思 没看清你的startdat是 02-12-10 呵呵 又要修改一下 select * from tb where replace((convert(varchar(10),getdate(),112)),'-','') between replace('startdate','-'),'') and replace('endate','-',''))这下应该没问题了 replace((convert(varchar(10),'01-12-04',112)),'-','') 能把01-12-04 转换成011204格式的
select distinct a1, (select top 1 startdate from table1 t1 where a1=t3.a1 and getdate() between startdate and enddate ) as startdate, (select top 1 enddate from table1 t2 where a1=t3.a1 and getdate() between startdate and enddate) as enddate from table1 t3
select A1,case when isnull(STARTDATE,'')<>'' then startdate end as statdate,case when isnull(ENDDATE,'')<>'' then ENDDAT end as ENDDATE from table union select a1=(select a1 from table group by a1 having count(a1)=1),isnull(STARTDATE,'')='' then startdate end as statdate,case when isnull(ENDDATE,'')='' then ENDDAT end as ENDDATE from table 如果有个时间段,加个条件就行了。
select * from T1 where getdate() between STARTDATE and ENDDATE as set1 union select * from T1 where Al not in (select Al from set1)and STARTDATE is null
select * from tablename where getdate()>=startdate and getdate()<=enddateunion select * from tablename where startdate is null and A1 not in(select A1 from tablename where getdate()>=startdate and getdate()<=enddate 应该是这样吧\! 小弟愚见,还请各位高手指点!!1
and STRARTDATE is not null
and ENDDATE is not null
getdate() >= isnull(STARTDATE,getdate()) and getdate() <= isnull(ENDDATE,getdate())
where getdate() between startdate and enddate
(getdate()>=STARTDATE and getdate()<=ENDDATE) or STARTDATE is null
(getdate()>=STARTDATE and getdate()<=ENDDATE) or STARTDATE is null or ENDDATE is null
where convert(char(8),getdate(),112) between convert(char(8),convert(datetime,'startdate),112) and convert(char(8),convert(datetime,'endate'),112)
呵呵 又要修改一下
select * from tb
where replace((convert(varchar(10),getdate(),112)),'-','') between replace('startdate','-'),'') and replace('endate','-',''))这下应该没问题了
replace((convert(varchar(10),'01-12-04',112)),'-','')
能把01-12-04 转换成011204格式的
有两条记录,
ABC 02-12-19 02-12-24
ABC
如果找到有当天在日期段内的就取
ABC 02-12-19 02-12-24
如果当天不在日期段内的就取
ABC
distinct a1,
(select top 1 startdate from table1 t1 where a1=t3.a1 and getdate() between startdate and enddate ) as startdate,
(select top 1 enddate from table1 t2 where a1=t3.a1 and getdate() between startdate and enddate) as enddate
from table1 t3
如果有多条记录包含了今天(getdate())
就取了其中一条(呵呵,你没有说明这种情况怎么取,要是有特殊要求,加一个order by就行了)
union select a1=(select a1 from table group by a1 having count(a1)=1),isnull(STARTDATE,'')='' then startdate end as statdate,case when isnull(ENDDATE,'')='' then ENDDAT end as ENDDATE from table
如果有个时间段,加个条件就行了。
where getdate() between STARTDATE and ENDDATE as set1
union
select * from T1
where Al not in (select Al from set1)and STARTDATE is null
where getdate()>=startdate and getdate()<=enddateunion
select * from tablename
where startdate is null
and A1 not in(select A1 from tablename
where getdate()>=startdate
and getdate()<=enddate
应该是这样吧\!
小弟愚见,还请各位高手指点!!1