34,575
社区成员
发帖
与我相关
我的任务
分享
Create Table ##表A
(
ID Int,
BeginTime Varchar(4),
EndTime Varchar(4)
)
Insert Into ##表A
Select 1,'','2010'
Union All
Select 2,'',''
Union All
Select 3,'1990',''
Union All
Select 3,'1980','1985'
Declare @QueryTime As varchar(7)
Set @QueryTime='1992-01'
Select * From ##表A
Where Cast(Case When BeginTime<>'' Then BeginTime+'-1-1' Else @QueryTime+'-1' End As DateTime)<=Cast(@QueryTime+'-1' As DateTime)
And Cast(Case When EndTime<>'' Then EndTime+'-1-1' Else @QueryTime+'-1' End As DateTime)>=Cast(@QueryTime+'-1' As DateTime)
Drop Table ##表A
;With Cte As
(
Select * From
(
Select 1 As Id, Null As BeginTime, '2010' As EndTime Union All
Select 2, Null,Null Union All
Select 3,'1990',Null Union All
Select 3,'1980','1985'
) As T
)
Select *
From Cte
Where Cast(Isnull(BeginTime,'1992') As Int) Between 1992 And 2001
Or Cast(Isnull(EndTime,'2001') As Int) Between 1992 And 2001
--以下查询假设时字符串
DECLARE @SearchTime DATETIME
SET @SearchTime='1992-01-01'
SELECT * FROM A
WHERE(BeginTime IS NULL OR BeginTime<=@SearchTime)
AND (EndTime IS NULL OR EndTime>=@SearchTime)
create table 表A
(ID int,BeginTime varchar(10),EndTime varchar(10))
insert into 表A
select 1,'空','2010' union all
select 2,'空','空' union all
select 3,'1990','空' union all
select 3,'1980','1985'
declare @x varchar(10) -- 查询参数
select @x='1992-01'
select *
from 表A
where left(@x,4) between
case when BeginTime='空' then '0' else BeginTime end
and case when EndTime='空' then '99999' else EndTime end
/*
ID BeginTime EndTime
----------- ---------- ----------
1 空 2010
2 空 空
3 1990 空
(3 row(s) affected)
*/