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)
 How to Get Succeeding Months

Author  Topic 

neo_bagsjol
Starting Member

6 Posts

Posted - 2014-01-26 : 23:12:18
Hello Expert Good day,

i would like to ask help for the this scenario,here it goes.. i have two columns which are start date and terms..when the user enter a date for example January 1,2014 and the term is 5 months to pay then the script will generate 5 months from the start date specified and every month should start 1 depends on the day selected..
below should be the sample output:
===========================
January 1,2014
Februay 1,2014
March 1,2014
April 1,2014
May 1,2014
============================

how can i achieved this on stored procedure.

Please help
thanks in advance

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-01-27 : 01:30:16
[code]


declare @dStartDate as date
declare @iNoOfMonth as int
set @dStartDate ='20140115'
set @iNoOfMonth=5
select @dStartDate=DATEADD(m,DATEDIFF(m,0,@dStartDate),0) --now it's 2014-01-01



;WITH aCTE
AS(
SELECT @dStartDate as FirstOfMonth,1 as lvl
UNION ALL
SELECT DATEADD(m,1,FirstOfMonth),lvl+1
FROM aCTE
WHERE aCTE.lvl<@iNoOfMonth
)

SELECT FirstOfMonth
FROM aCTE

[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-01-27 : 01:30:40
the output

2014-01-01
2014-02-01
2014-03-01
2014-04-01
2014-05-01


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

neo_bagsjol
Starting Member

6 Posts

Posted - 2014-01-27 : 01:48:52
thank you very much stepson for the script..question how about if i'm going to change the date instead of date 1 specified..i want to select another date like for example Jan 24,2014 so the output will be..

Jan 24,2014
Feb 24,2014
and soon..

thanks
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-01-27 : 01:58:47
hello,

comment this line

select @dStartDate=DATEADD(m,DATEDIFF(m,0,@dStartDate),0) --now it's 2014-01-01


S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

neo_bagsjol
Starting Member

6 Posts

Posted - 2014-01-27 : 02:12:20
WoW its working...another one i notice that Feb has no 30 its only 28 days and when i change date into 30 from January the output is look like this..

Jan 30,2014
Feb 28,2014
March 28,2014
April 28,2014

and soon..

its copies the date from Feb how can i prevent that? it should be
Jan 30,2014
Feb 28,2014
March 30,2014
April 30,2014
and soon.

thanks
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-01-27 : 02:15:30
[code]
;WITH aCTE
AS(
SELECT @dStartDate as FirstOfMonth,1 as lvl
UNION ALL
SELECT DATEADD(m,lvl,@dStartDate),lvl+1
FROM aCTE
WHERE aCTE.lvl<@iNoOfMonth
)

SELECT FirstOfMonth
FROM aCTE

[/code]


this line should be change
[code]
SELECT DATEADD(m,1,FirstOfMonth),lvl+1


with

SELECT DATEADD(m,lvl,FirstOfMonth),lvl+1

[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

neo_bagsjol
Starting Member

6 Posts

Posted - 2014-01-27 : 02:22:10
Your the Man Stepson its work as i expected..thanks a lot i hope this will help also others who seek for an answer..you got two thumbs up for me..
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-01-27 : 02:22:29
with welcome

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page
   

- Advertisement -