date increment not incrementing? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

date increment not incrementing?

Author

Topic

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-07-10 : 20:39:57

God day!

I have a working cte that produces the monthdate base on given dates.


USE MFR_Merchandise; 
declare @FromDate as date
declare @ToDate as date

set @FromDate='11-02-2011'
set @ToDate='02-24-2012'

; with rcte as
(
	select	FilterDate = dateadd(month, datediff(month, 0, @FromDate) + 1, -1)

	union all

	select	FilterDate = @ToDate

	union all

	select	FilterDate = dateadd(month, datediff(month, 0, FilterDate) + 2, -1)
	from	rcte
	where	dateadd(month, 1, FilterDate)	<= @ToDate
)
select	*
from	rcte
option (maxrecursion 0)

The above cte produces the result as below

I need to convert the said cte above into a plain temp table but when I increment the date? It seems its not working. Here is my code so far. The result must be the same as with cte.


USE MFR_Merchandise; 

declare @FromDate as date
declare @ToDate as date
declare @MonthDates  as  Table(MonthsFilter date    NOT NULL) 


set @FromDate='11-02-2011'
set @ToDate='02-24-2012'

while @FromDate < @ToDate
	begin
		insert into @MonthDates
		select MonthsFilter = dateadd(month, datediff(month, 0, @FromDate) + 1, -1)

		Set @FromDate = (SELECT Min(MonthsFilter) 
                         FROM   @MonthDates 
                         WHERE  MonthsFilter > @FromDate) 

	end
	
select * from @MonthDates

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-10 : 20:53:12

Not elegant, but it works:




declare @FromDate as date
declare @ToDate as date
declare @MonthDates  as  Table(MonthsFilter date    NOT NULL) 


set @FromDate='11-02-2011'
set @ToDate='02-24-2012'

while @FromDate < @ToDate
	begin
	
		insert into @MonthDates
		select dateadd(month, datediff(month, 0, @FromDate) + 1, -1)

		Set @FromDate = (SELECT min(MonthsFilter) 
                         FROM   @MonthDates 
                         WHERE  MonthsFilter > @FromDate) 

        set @FromDate = DATEADD(dd, 1, @FromDate)
        

	end
	
	update @MonthDates set MonthsFilter = @ToDate where MonthsFilter = (select MAX(MonthsFilter) from @MonthDates)
	
select * from @MonthDates

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-07-10 : 21:08:39

thank you tkizer.

This works!

quote:

Originally posted by tkizer

Not elegant, but it works:




declare @FromDate as date
declare @ToDate as date
declare @MonthDates  as  Table(MonthsFilter date    NOT NULL) 


set @FromDate='11-02-2011'
set @ToDate='02-24-2012'

while @FromDate < @ToDate
	begin
	
		insert into @MonthDates
		select dateadd(month, datediff(month, 0, @FromDate) + 1, -1)

		Set @FromDate = (SELECT min(MonthsFilter) 
                         FROM   @MonthDates 
                         WHERE  MonthsFilter > @FromDate) 

        set @FromDate = DATEADD(dd, 1, @FromDate)
        

	end
	
	update @MonthDates set MonthsFilter = @ToDate where MonthsFilter = (select MAX(MonthsFilter) from @MonthDates)
	
select * from @MonthDates

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources