Reputation: 4769
I am trying to implement a below scenario
I have two dates like below
declare @start DATE = '2011-05-21'
declare @end DATE = '2011-08-17'
My requirement is to generate a table like below based on these two dates
StartDate EndDate
-------------------------
2011-05-21 2011-05-31
2011-06-01 2011-06-30
2011-07-01 2011-07-31
2011-08-01 2011-08-17
--------------------
I tried the below CTE which is not giving me the output as I wish.
;with months (date1,date2)
AS
(
SELECT @start,eomonth(@start)
UNION ALL
SELECT DATEADD(month,1,date1),eomonth(DATEADD(month,1,date1))
from months
where DATEADD(month,1,date2)<=@end
)
select * from months
Its producing these data
2011-05-21 2011-05-31
2011-06-21 2011-06-30
2011-07-21 2011-07-31
So could you please help me to tweak it
Upvotes: 0
Views: 2739
Reputation: 46269
You can try to use some conditions in select
StartDate: If date1
is equal to @start
it might choose @start
otherwise you can try to use DATEADD(DAY,1,EOMONTH(date1 ,-1))
to get the first day of the month.
EndDate: If date2
is greater than @end
choose @end
otherwise get date2
.
The script as below
;with months (date1,date2)
AS
(
SELECT @start,eomonth(@start)
UNION ALL
SELECT DATEADD(month,1,date1),eomonth(DATEADD(month,1,date1))
from months
where DATEADD(month,1,date1)<=eomonth(@end)
)
select IIF(date1 = @start,@start,DATEADD(DAY,1,EOMONTH(date1 ,-1))) StartDate,
IIF(date2 > @end,@end,date2) EndDate
from months
Upvotes: 2