How to increment same calendar day next month

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 increment same calendar day next month

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-04-26 : 18:34:27

If i pass one month: with todays date(04/26/2010) then

the result has to be same date next month, is it possible?

same should happen if i say 3 months or a year.

same date after 3 monmths, same date after a year.

Set @FixedDays = 'Monthly' or 'Quarterly' or 'Yearly'
If @FixedDays = 'Monthly'
BEGIN
SET @RESULTDAYS = '05/26/2011'
END
ELSE If @FixedDays = 'Quarterly'
BEGIN
SET @RESULTDAYS = '07/26/2011'
END
ELSE If @FixedDays = 'Yearly'
BEGIN
SET @RESULTDAYS = '04/26/2012'
END

Thanks for the helpful info.

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 18:37:18

OK...look at DATEADD function in BOL

However...are you sure you want the same day?

Months have diffent days...which gets worse by quarter and worse by year (adding in leaps)

Never mind that Tsunamis mess with our calendar as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-26 : 18:40:27

Sure, just use dateadd

DECLARE @FixedDays varchar(20)
DECLARE @RESULTDAYS datetime
DECLARE @inDate datetime

SET @inDate = '20110426'

Set @FixedDays = 'Monthly' or 'Quarterly' or 'Yearly'
If @FixedDays = 'Monthly'
BEGIN
SET @RESULTDAYS = DATEADD(month,1,@inDate)
END
ELSE If @FixedDays = 'Quarterly'
BEGIN
SET @RESULTDAYS = DATEADD(q,1,@inDate)
END
ELSE If @FixedDays = 'Yearly'
BEGIN
SET @RESULTDAYS = DATEADD(y,1,@inDate)
END

select @inDate,@FixedDays,@resultdays

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 18:42:30

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-04-26 : 22:14:38

You will have to decide the rule for when the same day of month does not exist in the next month (see 2011-01-31 below).

select
	a.DT,
	NextMonth = dateadd(month,1,a.DT)
from
	( -- Test Data
	select DT = convert(date,'20110131')    union all
	select DT = convert(date,'20110215')    union all
	select DT = convert(date,'20121231')
	) a
order by
	a.DT

Results:

DT         NextMonth
---------- ----------
2011-01-31 2011-02-28
2011-02-15 2011-03-15
2012-12-31 2013-01-31

(3 row(s) affected)

CODO ERGO SUM

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-04-27 : 12:07:50

Hello Jim,
Thanks a lot for the script, but has one problem.
Monthly & quarterly both working except Yearly.

When i choose yearly the year remaining same 2011, instead of showing 2012.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 12:16:32

SET @RESULTDAYS = DATEADD(year,1,@inDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources