June 3, 2014 at 12:56 am
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!!
June 3, 2014 at 1:17 am
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
Change is inevitable... Change for the better is not.
June 3, 2014 at 2:31 am
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
June 3, 2014 at 8:42 am
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;
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy