declare @dt1 datetime,@dt2 datetime set @dt1='05/10/2011' set @dt2='05/25/2011' select min(a.dt)begindate,max(a.dt)enddate from ( select dateadd(d,number,convert(varchar(8),@dt1,120)+'1')dt from master..spt_values where type='p' and month(dateadd(d,number,convert(varchar(8),@dt1,120)+'1'))=month(@dt1) )a,( select @dt1 dt union all select @dt2 union all select dateadd(m,1,convert(varchar(8),@dt2,120)+'1') )b where a.dt<b.dt group by b.dt /* begindate enddate ----------------------- ----------------------- 2011-05-01 00:00:00.000 2011-05-09 00:00:00.000 2011-05-01 00:00:00.000 2011-05-24 00:00:00.000 2011-05-01 00:00:00.000 2011-05-31 00:00:00.000(3 行受影响)*/
老兄,Begin Date 不是月初第一天,是每次更新的时间。
declare @dt1 datetime,@dt2 datetime set @dt1='05/10/2011' set @dt2='05/25/2011' ;with cte as( select convert(varchar(8),@dt1,120)+'01' dt union all select @dt1 union all select @dt2 union all select dateadd(m,1,convert(varchar(8),@dt2,120)+'1') )select a.dt startdate,b.dt enddate from cte a inner join cte b on a.dt<b.dt and not exists(select 1 from cte where dt>a.dt and dt<b.dt) /* startdate enddate ----------------------- ----------------------- 2011-05-01 00:00:00.000 2011-05-10 00:00:00.000 2011-05-10 00:00:00.000 2011-05-25 00:00:00.000 2011-05-25 00:00:00.000 2011-06-01 00:00:00.000(3 行受影响)*/
修正: declare @dt1 datetime,@dt2 datetime set @dt1='05/10/2011' set @dt2='05/25/2011' ;with cte as( select convert(varchar(8),@dt1,120)+'01' dt union all select @dt1 union all select @dt2 union all select dateadd(m,1,convert(varchar(8),@dt2,120)+'1') )select a.dt startdate,dateadd(d,-1,b.dt) enddate from cte a inner join cte b on a.dt<b.dt and not exists(select 1 from cte where dt>a.dt and dt<b.dt) /* startdate enddate ----------------------- ----------------------- 2011-05-01 00:00:00.000 2011-05-09 00:00:00.000 2011-05-10 00:00:00.000 2011-05-24 00:00:00.000 2011-05-25 00:00:00.000 2011-05-31 00:00:00.000(3 行受影响)*/
declare @dt1 datetime,@dt2 datetime set @dt1='05/10/2011' set @dt2='05/25/2011' select dateadd(mm,datediff(mm,0,@dt1),0),dateadd(dd,-1,@dt1) union all select @dt1,dateadd(dd,-1,@dt2) union all select @dt2,dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,@dt2)+1, 0)) ------------------------------------------------------ ------------------------------------------------------ 2011-05-01 00:00:00.000 2011-05-09 00:00:00.000 2011-05-10 00:00:00.000 2011-05-24 00:00:00.000 2011-05-25 00:00:00.000 2011-05-31 23:59:59.997(所影响的行数为 3 行)
declare @d1 datetime ,@d2 datetime ; select @d1='05/10/2011' ,@d2='05/25/2011' ;select CONVERT(CHAR(8),@d1,120)+'01' AS StartDate,@d1-1 AS EndDate union all select @d1,@d2-1 union all select @d2,DATEADD(MM,1,CONVERT(CHAR(8),@d1,120)+'01')-1 order by StartDate desc;/*结果 StartDate EndDate ----------------------- ----------------------- 2011-05-25 00:00:00.000 2011-05-31 00:00:00.000 2011-05-10 00:00:00.000 2011-05-24 00:00:00.000 2011-05-01 00:00:00.000 2011-05-09 00:00:00.000(3 行受影响) */
declare @UpDateTimeTB table(Updatetime datetime) insert @UpDateTimeTB select '05/05/2011' union all --改这里..想分多少个都可以.. select '05/10/2011' union all select '05/25/2011'declare @MonthDayTB table(num int,[Day] datetime) insert @MonthDayTB select number-Row_Number()over(order by number) as num,dateadd(day,number,M) as [Day] from master..spt_values left join (select top 1 convert(varchar(8),Updatetime,120)+'01' as M from @UpDateTimeTB) as UTB on 1=1 where type='p' and number between 0 and datepart(day,dateadd(day,-1,dateadd(month,1,M)))-1 and not exists (select 1 from @UpDateTimeTB where dateadd(day,number,M)=Updatetime)select case when datepart(day,min([Day]))=1 then min([Day]) else dateadd(day,-1,min([Day])) end BEGIN_DATE, max([Day]) END_DATE from @MonthDayTB group by num (3 row(s) affected)(28 row(s) affected) BEGIN_DATE END_DATE ----------------------- ----------------------- 2011-05-01 00:00:00.000 2011-05-04 00:00:00.000 2011-05-05 00:00:00.000 2011-05-09 00:00:00.000 2011-05-10 00:00:00.000 2011-05-24 00:00:00.000 2011-05-25 00:00:00.000 2011-05-31 00:00:00.000(4 row(s) affected)哈哈,小弟弄得有点复杂,不过就是想试着弄个动态的,从@UpDateTimeTB的加日期,就可以分开N+1段, 但不能跨月哦 :P
老兄,问题解决,但是这句不明白,请解释一下:and not exists(select 1 from cte where dt>a.dt and dt<b.dt) SELECT 1 FROM #TEMP_DATE WHERE U_DATE > a.U_DATE AND U_DATE < b.U_DATE
set @dt1='05/10/2011'
set @dt2='05/25/2011'
select min(a.dt)begindate,max(a.dt)enddate from (
select dateadd(d,number,convert(varchar(8),@dt1,120)+'1')dt from master..spt_values where type='p' and month(dateadd(d,number,convert(varchar(8),@dt1,120)+'1'))=month(@dt1)
)a,(
select @dt1 dt union all select @dt2 union all select dateadd(m,1,convert(varchar(8),@dt2,120)+'1')
)b where a.dt<b.dt group by b.dt
/*
begindate enddate
----------------------- -----------------------
2011-05-01 00:00:00.000 2011-05-09 00:00:00.000
2011-05-01 00:00:00.000 2011-05-24 00:00:00.000
2011-05-01 00:00:00.000 2011-05-31 00:00:00.000(3 行受影响)*/
set @dt1='05/10/2011'
set @dt2='05/25/2011'
;with cte as(
select convert(varchar(8),@dt1,120)+'01' dt
union all select @dt1
union all select @dt2
union all select dateadd(m,1,convert(varchar(8),@dt2,120)+'1')
)select a.dt startdate,b.dt enddate from cte a inner join cte b on a.dt<b.dt and not exists(select 1 from cte where dt>a.dt and dt<b.dt)
/*
startdate enddate
----------------------- -----------------------
2011-05-01 00:00:00.000 2011-05-10 00:00:00.000
2011-05-10 00:00:00.000 2011-05-25 00:00:00.000
2011-05-25 00:00:00.000 2011-06-01 00:00:00.000(3 行受影响)*/
declare @dt1 datetime,@dt2 datetime
set @dt1='05/10/2011'
set @dt2='05/25/2011'
;with cte as(
select convert(varchar(8),@dt1,120)+'01' dt
union all select @dt1
union all select @dt2
union all select dateadd(m,1,convert(varchar(8),@dt2,120)+'1')
)select a.dt startdate,dateadd(d,-1,b.dt) enddate from cte a inner join cte b on a.dt<b.dt and not exists(select 1 from cte where dt>a.dt and dt<b.dt)
/*
startdate enddate
----------------------- -----------------------
2011-05-01 00:00:00.000 2011-05-09 00:00:00.000
2011-05-10 00:00:00.000 2011-05-24 00:00:00.000
2011-05-25 00:00:00.000 2011-05-31 00:00:00.000(3 行受影响)*/
set @dt1='05/10/2011'
set @dt2='05/25/2011'
select dateadd(mm,datediff(mm,0,@dt1),0),dateadd(dd,-1,@dt1)
union all
select @dt1,dateadd(dd,-1,@dt2)
union all
select @dt2,dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,@dt2)+1, 0))
------------------------------------------------------ ------------------------------------------------------
2011-05-01 00:00:00.000 2011-05-09 00:00:00.000
2011-05-10 00:00:00.000 2011-05-24 00:00:00.000
2011-05-25 00:00:00.000 2011-05-31 23:59:59.997(所影响的行数为 3 行)
declare
@d1 datetime
,@d2 datetime
;
select
@d1='05/10/2011'
,@d2='05/25/2011'
;select CONVERT(CHAR(8),@d1,120)+'01' AS StartDate,@d1-1 AS EndDate
union all
select @d1,@d2-1
union all
select @d2,DATEADD(MM,1,CONVERT(CHAR(8),@d1,120)+'01')-1
order by StartDate desc;/*结果
StartDate EndDate
----------------------- -----------------------
2011-05-25 00:00:00.000 2011-05-31 00:00:00.000
2011-05-10 00:00:00.000 2011-05-24 00:00:00.000
2011-05-01 00:00:00.000 2011-05-09 00:00:00.000(3 行受影响)
*/
declare @UpDateTimeTB table(Updatetime datetime)
insert @UpDateTimeTB
select '05/05/2011' union all --改这里..想分多少个都可以..
select '05/10/2011' union all
select '05/25/2011'declare @MonthDayTB table(num int,[Day] datetime)
insert @MonthDayTB
select number-Row_Number()over(order by number) as num,dateadd(day,number,M) as [Day] from master..spt_values
left join (select top 1 convert(varchar(8),Updatetime,120)+'01' as M from @UpDateTimeTB) as UTB on 1=1
where type='p' and number between 0 and datepart(day,dateadd(day,-1,dateadd(month,1,M)))-1
and not exists (select 1 from @UpDateTimeTB where dateadd(day,number,M)=Updatetime)select case when datepart(day,min([Day]))=1 then min([Day]) else dateadd(day,-1,min([Day])) end BEGIN_DATE,
max([Day]) END_DATE from @MonthDayTB group by num
(3 row(s) affected)(28 row(s) affected)
BEGIN_DATE END_DATE
----------------------- -----------------------
2011-05-01 00:00:00.000 2011-05-04 00:00:00.000
2011-05-05 00:00:00.000 2011-05-09 00:00:00.000
2011-05-10 00:00:00.000 2011-05-24 00:00:00.000
2011-05-25 00:00:00.000 2011-05-31 00:00:00.000(4 row(s) affected)哈哈,小弟弄得有点复杂,不过就是想试着弄个动态的,从@UpDateTimeTB的加日期,就可以分开N+1段,
但不能跨月哦 :P
SELECT 1
FROM #TEMP_DATE
WHERE U_DATE > a.U_DATE
AND U_DATE < b.U_DATE