February 27, 2012 at 7:47 am
Hello All
I have FromDate and End Date in stored procedure and I need to break this date with 12 month Difference and I need to stored this date in Temp table ..how can I Do ?
like
@fromDate =2010-01-01
@End Date = 2012-06-01
Then
Output in Temporaty Table is
fromDate End Date
2010-01-01 2011-01-01
2011-01-01 2012-01-01
2012-01-01 2012-06-01
Thanks
Bhavesh
February 27, 2012 at 8:05 am
One way of doing it is to use a recursive CTE that creates the list of dates and use the CTE in select into statement. Here is an example:
declare @FromDate datetime
declare @EndDate datetime
set @FromDate ='20100101'
set @EndDate = '20120601';
with MyCTE as (
select dateadd(yy,1,@FromDate) as DateCol
union all
select dateadd(yy,1,DateCol)
from MyCTE
where dateadd(yy,1,DateCol) < @EndDate)
select DateCol into #TmpTable
from MyCTE
union
select @EndDate
select * from #TmpTable
go
drop table #TmpTable
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 27, 2012 at 9:04 am
Thank You So much
🙂
Thanks
Bhavesh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply