Glad that worked :)
I have a suggestion as to your next problem.
First create a new variable
Then set the variable to the string you were converting.
Then do a convert on that variable.
@string_to_convert varchar(10)
SELECT @string_to_convert = @AdvMonth + '/01/'+ @rptyear
SELECT @chkdate = CONVERT(datetime, @string_to_convert)
Ali
> Ok that worked Horray! and thank you so much for stating the obvious
> that I was too blind to see.
>
> Ok with that I have another problem kind of the same thing, but I ma
> now building a date sting to filter but I keep getting an error:
> Syntax error converting datetime from character string.
>
> Here is the code:
>
> CREATE PROCEDURE GetFirstRenewalActual AS
>
>
> DECLARE
> @rptMONTH int,
> @rptmonth_v varchar(2),
> @AdvMONTH varchar(2),
> @people int,
> @company int,
> @rptYEAR varchar(4),
> @did int,
> @rentable varchar(10),
> @total int,
> @chkdate datetime
>
>
> IF object_id('#FirstRunTotals') is not null
> BEGIN
> DROP TABLE #FirstRunTotals;
> END
> ELSE
> BEGIN
> CREATE TABLE #FirstRunTotals
> (
> rptrenewal varchar(7) NULL,
> people int,
> company int,
> total int
> );
> END
>
>
> DECLARE didCursor cursor local static
>
> FOR
> SELECT rptid
> FROM RenewalMonths
> WHERE completed = 1
> ORDER BY rptyear,rptmonth
>
>
> open didCursor
>
> fetch didCursor into @did
>
> WHILE @@fetch_status=0
> BEGIN
>
> SELECT @rptyear=rptyear,@rptmonth=rptmonth,@rptmonth_v = rptmonth
> FROM RenewalMonths
> WHERE rptid = @did
>
> SELECT @AdvMonth = @rptmonth +1
>
> SELECT @rentable = @[EMAIL PROTECTED]'_ren'
>
> SELECT @chkdate = CAST(@AdvMonth+'/1/'[EMAIL PROTECTED] as datetime)
>
>
> --- DIES RIGHT HERE !!!
>
> SELECT
> (
> SELECT count(*)
> FROM persondem(nolock)
> WHERE expdate > @chkdate
> AND CAST(eaanum as integer) IN (
> SELECT CAST(EAANUM as integer)
> [EMAIL PROTECTED])
> )+(
> SELECT count(*)
> FROM companydem(nolock)
> WHERE expdate > @chkdate
> AND CAST(eaanum as integer) IN (
> SELECT CAST(EAANUM as integer)
> [EMAIL PROTECTED])
> )
>
>
> INSERT INTO #FirstRunTotals
> (rptrenewal,people,company,total)
> VALUES
> (@rptyear+'-'[EMAIL PROTECTED],@people,@company,@total)
>
> fetch next FROM didCursor into @did
>
> END
>
> close didCursor
>
> deallocate didCursor
>
> SELECT * FROM #FirstDayTotals
> GO
>
>
>
>
>
>
> -----Original Message-----
> From: Ali Awan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 23, 2004 11:21 AM
> To: CF-Talk
> Subject: Re: SQL: Passing vars to a LIKE statement
>
>
> Eric,
>
> Can you try just doing this first?
> AND demographicvaluedesc LIKE @month+'%'
>
> And do you still get the error?
>
> Then also try this, don't know if it will solve the problem, but it
> looks like SQL is actually trying to do an arithmetic add on the
> values, rather than concatenating the strings in the searchstring.
>
> Try using a different variable for year and declare it as a varchar.
> @year_temp varchar(4). Put spaces before and after the '+' sign,
> don't know if it'll help.
>
> and then in the searchstring do this again.
> AND demographicvaluedesc LIKE @month + '%' + @year_temp + '%'
>
> Hope this works,
> Ali
>
> >Pass the percent signs as part of your var... Don't know why it
> works
> >but it does. There is a way to do it in a stored proc but I don't
> >remember off the top of my head.
> >
> >
> >
> > _____
> >
> >From: Eric Creese [mailto:[EMAIL PROTECTED]
> >Sent: Wednesday, September 22, 2004 4:15 PM
> >To: CF-Talk
> >Subject: RE: SQL: Passing vars to a LIKE statement
> >
> >
> >
> >thanks but it is not helping, here is my stored proc and the error
> msg I
> >receive
> >
> >
> >CREATE PROCEDURE GetMonthlyJoins AS
> >
> >DECLARE
> >@MONTH varchar(3),
> >@MONTHNBR int,
> >@YEAR int,
> >@did datetime,
> >@count int,
> >@likevar varchar(25)
> >
> >
> >IF object_id('#MonthlyJoins') is not null
> >BEGIN
> >TRUNCATE TABLE #MonthyJoins;
> >END
> >ELSE
> >BEGIN
> > CREATE TABLE #MonthyJoins
> > (
> > rptmonth varchar(3),
> > rptmonthnbr int,
> > rptyear int,
> > total int
> > );
> >END
> >
> >DECLARE didCursor cursor local static
> >
> >
> >FOR
> >SELECT DISTINCT rptdate
> >FROM ALL_DAILY_TOTALS
> >WHERE convert(varchar(10),rptdate,101) like '%/01/%'
> >ORDER BY rptdate
> >
> >open didCursor
> >
> >
> >fetch didCursor into @did
> >
> >
> >WHILE @@fetch_status=0
> >BEGIN
> >
> >SET @year = (SELECT DATEPART(year, @did))
> >SET @month =(SELECT UPPER(LEFT(DATENAME(month, @did),3)))
> >SET @monthnbr =(SELECT MONTH(@did))
> >
> >SELECT @count=COUNT(*)
> >FROM avectraprod.dbo.persondemographic(nolock)
> >WHERE demographicgroupid =4
> >AND demographicitemid =7
> >AND demographicvaluedesc LIKE @month+'%'[EMAIL PROTECTED]'%'
> >
> >INSERT INTO #MonthyJoins
> >( rptmonth,rptmonthnbr,rptyear, total )
> >VALUES
> >(@month,@monthnbr,@year,@count)
> >
> >
> >fetch next FROM didCursor into @did
> >
> >
> >END
> >
> >
> >close didCursor
> >
> >
> >deallocate didCursor
> >
> >
> >SELECT * FROM #MonthyJoins
> >
> >
> >GO
> >
> >
> >Error Message:
> >Server: Msg 245, Level 16, State 1, Procedure GetMonthlyJoins, Line
> 51
> >Syntax error converting the varchar value 'FEB%' to a column of data
> >type int.
> >
> >
> >-----Original Message-----
> >From: Ali Awan [mailto:[EMAIL PROTECTED]
> >Sent: Wednesday, September 22, 2004 1:21 PM
> >To: CF-Talk
> >Subject: Re: SQL: Passing vars to a LIKE statement
> >
> >> I need to pass some varibles into a LIKE clause in a SELECT
> statement.
> >
> >> I am having trouble with the format.
> >>
> >> AND valuedesc LIKE '[EMAIL PROTECTED]@year%' is what I was doing and know
> this
> >> is wrong cause it see that garb as a literal string. How do I pass
>
> >> this so the vars
> >show?
> >
> >Eric,
> >
> >I recently ran into this same problem recently. The correct way to
> do
> >this is:
> >AND valuedesc LIKE '%'[EMAIL PROTECTED]'%'[EMAIL PROTECTED]'%'
> >
> >You need to put the "+" signs in and remember to put the single
> quotes
> >properly. Whenever you use variables, you have to build the
> statement
> >as a string.
> >
> >Hope this helps,
> >Ali
> > _____
> >
> > _____
> _____
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
