if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[日期串] varchar(28))
insert [tb]
select 1,'120312;120523;120727;' union all
select 2,'120623;120719;' union all
select 3,'120930;' union all
select 4,'120623;120719;121011;130229;'
go

declare @sdt varchar(10),@edt varchar(10)
select @sdt='120722',@edt='121002'

select a.*
from tb a
join (
  select right(convert(varchar(10),dateadd(dd,number,@sdt),112),6) as dt
  from master..spt_values
  where type='P'
  and dateadd(dd,number,@sdt)<=@edt
) b 
on charindex(';'+b.dt+';',';'+a.日期串)>0

/**
ID          日期串
----------- ----------------------------
1           120312;120523;120727;
3           120930;

(2 行受影响)
**/

------解决方案--------------------

SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    ID INT,
    日期串 VARCHAR(100)
)
GO
INSERT INTO tba
SELECT 1, '120312;120523;120727;' UNION
SELECT 2, '120623;120719;' UNION
SELECT 3, '120930;' UNION
SELECT 4, '120623;120719;121011;130229;'
GO

SELECT ID,日期串
FROM tba AS A,(
SELECT CONVERT(VARCHAR(6),DATEADD(DAY,number,'120722'),12) AS date
FROM master..spt_values
WHERE type = 'P' AND DATEADD(DAY,number,'120722') <= '121002') AS B
WHERE CHARINDEX(';' + RTRIM(B.date) + ';',';' + A.日期串) > 0

ID    日期串
1    120312;120523;120727;
3    120930;

------解决方案--------------------

SQL code

--来个05XML

create table tb(id int,date varchar(100))
insert into tb
select 1 ,'120312;120523;120727;' union all
select 2 ,'120623;120719;' union all
select 3 ,'120930;' union all
select 4 ,'120623;120719;121011;130229;'
go

select a.id,b.date
from (
    select id,date = convert(xml,'<v>' + REPLACE(date, ';', '</v><v>') + '</v>') from tb
) A
    outer apply(
    select date = N.v.value('.', 'varchar(100)') from A.date.nodes('/v') N(v)
) B
where b.date between '120722' and '121002'
group by a.id,b.date

drop table tb

/***************

id          date
----------- ----------------------------------------------------------------
1           120727
3           120930

(2 行受影响)

------解决方案--------------------
SQL code

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[ID] int,
[日期串] varchar(27)
)
go
insert [test]
select 1,'120312;120523;120727;' union all
select 2,'120623;120719;' union all
select 3,'120930;' union all
select 4,'120623;120719;121011;130229'
go

--方法一：系统表构造

select 
    a.* 
from 
    test a
inner join
    (
select 
    right(convert(varchar(10),dateadd(dd,number,'20120722'),112),6) as [date]
from 
    master..spt_values
where 
    type='p' 
    and number between 0 and datediff(dd,'20'+'120722','20'+'121002'))t
on 
    charindex(t.[date],a.[日期串])>0

/*
ID    日期串
----------------------------------
1    120312;120523;120727;
3    120930;
*/
--方法二：使用CTE拆分字符串
with T (id,P1,P2) as
(
    select 
        id,
        charindex(';',';'+[日期串]),
        charindex(';',[日期串])+1 
    from 
        [test]
    union all
    select 
        a.id,
        b.P2,
        charindex(';',[日期串],b.P2)+1 
    from 
        [test] a 
    join T b 
    on a.id=b.id 
    where 
        charindex(';',[日期串],b.P2)>0
),m
as(
    select 
        a.id,
        [日期串]=substring(a.[日期串],b.P1,b.P2 - b.P1 - 1) 
    from 
        [test] a 
    join 
        T b 
    on a.id=b.id 
)
select 
     b.*
from 
    m inner join test b
on m.ID=b.ID
where 
    m.[日期串