Add month to a Date

Question

Add month to a Date

niladri.primalink

SSCommitted

Points: 1751
More actions
June 3, 2014 at 12:56 am

#308755

Hi All,
I am trying to add month to a date. Here is my code
declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all
SELECT TransactionDate=DATEADD(MONTH,1,CONVERT(date, CONVERT(varchar(4), YEAR(TransactionDate))
+'-'+CONVERT(varchar(2),MONTH(TransactionDate))
+'-'+ CONVERT(varchar(2),DATEPART(day, @CollectionDate)))),
RemainingTransaction+1
FROM CTemp
WHERE RemainingTransaction < 9
)
select * from CTemp
it is working fine. But when I am giving date '10-30-2014' it shows me the error
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
I can understand the problem that it is for the month of February. But How do I overcome the situation?
Please help me....
Thanks in advance!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

Just do the DATEADD to the Transaction date without all the individual Year, Month, and Day stuff. If the final result needs a particular format and can't be done from the front end where it's supposed to be formatted, then format it only after you've added the month.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

niladri.primalink SSCommitted Points: 1751 More actions · Answer 2

Hi Jeff,

Thanks for your reply...the issue is now solved. Here is the code

declare @CollectionDate date='10-30-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all
SELECT TransactionDate=DATEADD(MONTH,RemainingTransaction,@CollectionDate),
RemainingTransaction+1
FROM CTemp
WHERE RemainingTransaction < 9
)
select * from CTemp

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

I'm surprised that Jeff didn't mention that recursive CTEs that count are a form of hidden RBAR (and he usually mentions it even with such few rows).

Here you have a couple of alternatives to create a Tally table on the fly. And you'll find more along with detailed information in this article: http://www.sqlservercentral.com/articles/T-SQL/74118/

declare @CollectionDate date='10-30-2014';

select @CollectionDate;

WITH CTemp AS (

SELECT DATEADD(MONTH,n - 1,@CollectionDate) AS TransactionDate,

n AS RemainingTransaction

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))x(n)

)

select * from CTemp;

DECLARE @Rows int = 9;

WITH cteTally AS(

SELECT TOP (@Rows) ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) n

FROM master.sys.all_columns

),

CTemp AS (

SELECT DATEADD(MONTH,n - 1,@CollectionDate) AS TransactionDate,

n AS RemainingTransaction

FROM cteTally

)

select * from CTemp;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2