Error converting Varchar to Int - SQL 2000

Hello, I'm having problems with this piece of Transact SQL. What I
need to do is to get a date 2 months ahead of an entered date.
However, I am not supposed to use this: @.dt+60 (in order to get 60 days
exactly). Consequently, if I've entered '12/21/05' as the original
date, I need to get the 2 month date as '2/21/06' regardless if they're
60 days or not. As you can see, the only thing that the new date will
be changed is going to be the month. Thus, the dates would convert as
follows: '5/5/05' to '7/5/05', '2/21/05' to '4/21/05' and so on except
for the months 11 and 12, which in that case would be 1 and 2 of the
following year.
This is what I have so far.
DECLARE @.dt DATETIME
DECLARE @.2dt VARCHAR
SET @.dt = '12/14/06'
SET @.2dt =
CASE
WHEN DATEPART(mm, @.dt) =11 THEN '11/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)+1
WHEN DATEPART(mm, @.dt) =12 THEN '12/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)+1
ELSE DATEPART(mm, @.dt) +2 & '/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)
END
print @.2dt
When I test it in the query analyzer, I get the this error: Syntax
error converting the varchar value '12/' to a column of data type int.
I tried to use the CAST and CONVERT function, it did not work. Perhaps
I was doing it wrong.
Any help would be appreciated.Doesn't this work for you?
DECLARE @.dt DATETIME
DECLARE @.2dt datetime
SET @.dt = '12/14/06'
SET @.2dt = dateadd(m,2,@.dt)
print convert(varchar,@.2dt)
http://sqlservercode.blogspot.com/|||Gosh! Thank you! This is exactly what I was looking for! You're a
genius!
I had this code as part of an Access query that I needed to convert to
SQL 2000 and it was driving me crazy. I'm so glad SQL has a function
that does this.
Thanks again!
JR
SQL wrote:
> Doesn't this work for you?
> DECLARE @.dt DATETIME
> DECLARE @.2dt datetime
> SET @.dt = '12/14/06'
> SET @.2dt = dateadd(m,2,@.dt)
> print convert(varchar,@.2dt)
>
> http://sqlservercode.blogspot.com/|||note: the reason for the error is that the '&' operator in sql is the
bitwise AND operator - not a string concatenation operator
so it was trying to convert '12/' to an int before performing the
bitwise AND.
ILCSP@.NETZERO.NET wrote:
> Hello, I'm having problems with this piece of Transact SQL. What I
> need to do is to get a date 2 months ahead of an entered date.
> However, I am not supposed to use this: @.dt+60 (in order to get 60 days
> exactly). Consequently, if I've entered '12/21/05' as the original
> date, I need to get the 2 month date as '2/21/06' regardless if they're
> 60 days or not. As you can see, the only thing that the new date will
> be changed is going to be the month. Thus, the dates would convert as
> follows: '5/5/05' to '7/5/05', '2/21/05' to '4/21/05' and so on except
> for the months 11 and 12, which in that case would be 1 and 2 of the
> following year.
> This is what I have so far.
> DECLARE @.dt DATETIME
> DECLARE @.2dt VARCHAR
> SET @.dt = '12/14/06'
> SET @.2dt =
> CASE
> WHEN DATEPART(mm, @.dt) =11 THEN '11/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)+1
> WHEN DATEPART(mm, @.dt) =12 THEN '12/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)+1
> ELSE DATEPART(mm, @.dt) +2 & '/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)
> END
>
> print @.2dt
>
> When I test it in the query analyzer, I get the this error: Syntax
> error converting the varchar value '12/' to a column of data type int.
> I tried to use the CAST and CONVERT function, it did not work. Perhaps
> I was doing it wrong.
> Any help would be appreciated.
>