How to Get Succeeding Months - SQL Server Forums

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

stepson
Aged Yak Warrior

545 Posts

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

the output

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

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

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

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

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

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..

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources