out of range smalldatetime value 1

[link9]

Ok guys, I'm really hoping one of you can pull me out on this one -- each time I think I get it solved, it rears its disgusting nasty revulting head again --

Here's the deal:

I've got a SPROC that may or may not need some extra work to its where clause depending on the current state of the webpage that is calling it -- This depends on three different things: SVP, VPO, and DM -- if any of those variables have a value in the webpage, then the SPROC must filter by that criteria in order to return what I need.

So, I declared four variables in the SPROC, @svp, @vpo, & @dm -- and the fourth, @where, will hold the extra where clause (if any) if any of those other three variables get sent a value. Here's a bit of code:

@svp		int		= 0,
@vpo		int		= 0,
@dm		int		= 0,
@where	varchar(100)	= ''

-^-^-^-^-^-^-^-^-^-^-^-^-^-^-^

IF @svp <> 0 
  SELECT @where='(svp=' + CAST(@svp AS varchar) + ') AND '
IF @vpo <> 0 
  SELECT @where='(vpo=' + CAST(@vpo AS varchar) + ') AND '
IF @dm <> 0
  SELECT @where='(dm=' + CAST(@dm AS varchar) + ') AND '

Ok, so notice that @where is initially assigned a value of '' (empty string) and if any of the other three variables get a value, then I assign a string to @where, and then I use @where like this:

SELECT @complete = COUNT(RespondentUniqueKey) FROM cscResponse2001 INNER JOIN cMasterDemo ON cscResponse2001.ourID = cMasterDemo.[id] WHERE + @where + responseDate >= '06/01/01' AND responseDate <= '06/30/01' AND method = @method AND firstMail >= @startDate AND firstMail <= @endDate AND accountType = @accountType

So that it gets inserted right into the middle of that select statement -- if it's blank, then no harm, no foul (and in fact, that's the only time it's working), but if it's not blank, then its contents are read into the select statement and translated into my desired filtered results.

Are you with me so far? Good. Now comes the trouble...

Now don't ask me why, but everything was working just fine before I added all this where crap... but now, if @where gets a value, my @startDate and @endDate start throwing this error:

-----
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
-----

and I'm assigning those two variables in an ASP page like this:

comObj.Parameters(&quot;@startDate&quot = &quot;06/01/01&quot;
comObj.Parameters(&quot;@endDate&quot = &quot;06/30/01&quot;

which seemed to be working OK before I tossed this where stuff into the mix -- ***sigh***

And of course, the error is thrown right when I try to:
comObj.Execute

So then, I thought, well, I'll just CAST() them into a smalldatetime once I get them into the SPROC -- no dice, though. Same error.


So basically, I'm thinking that it's something else besides the datetime hicky dick (cuz like I said, it WAS working) -- and this is the first time I've ever tried to use a variable as part of my SELECT statement inside the SPROC itself, and to be honest, I'm not even sure that such a thing is possible (Please, oh please, let it be possible) --

So there you have it.... I know it was a bit long winded, but I wanted to get it all out there and pray that one of my good Tek-Tips SQL Server gurus out there in la-la land could set me back on the straight and narrow.

Thanks for any insight. I'm up against a wall here.

:-I
Paul Prewett

 

[tlbroadbent]

You can't insert the WHERE clasue in the manner you are trying. You'lll need to dynamically create and execute a SQL statement. Try the following.

DECLARE @sqlstatement nvarchar(4000)

SET @sqlstatement=
'SELECT @complete = COUNT(RespondentUniqueKey)
FROM cscResponse2001
INNER JOIN cMasterDemo
ON cscResponse2001.ourID = cMasterDemo.[id]
WHERE ' + @where + 'responseDate >= ''06/01/01''
AND responseDate <= ''06/30/01''
AND method = ''' + @method + '''
AND firstMail >= '''+ @startDate + '''
AND firstMail <= ''' + @endDate + '''
AND accountType = ''' + @accountType + ''''

Execute @sqlstatement

NOTE: I assume @method and @accountType are character data. If not, remove the single quotes. I hope all the syntax is correct. I can't test it right now. Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[link9]

Terry, you truly are the man when it comes to SQL (and who knows, maybe something else, too) ;-). Always a pleasure. I think I'll tackle this in the morning. Thanks again.

 

[link9]

Ok, here's all my declarations and my statement:

@month varchar(10),
@complete int OUTPUT,
@responseRate float = 0 OUTPUT,
@sent int = 0,
@method int = 0,
@startDate smalldatetime = NULL,
@endDate smalldatetime = NULL,
@accountType int,
@svp int = 0,
@vpo int = 0,
@dm int = 0,
@total tinyint = 0,
@sqlStatement varchar(4000) = ''

SET @sqlStatement = 'SELECT @complete = COUNT(RespondentUniqueKey) FROM cscResponse2001 INNER JOIN cMasterDemo ON cscResponse2001.ourID = cMasterDemo.[id] WHERE responseDate >= ''06/01/01'' AND responseDate <= ''06/30/01'' AND method = ' + @method + ' AND firstMail >= ''' + @startDate + ''' AND firstMail <= ''' + @endDate + ''' AND accountType = ' + @accountType + ' AND dm = ' + @dm

and here's my error message from the webpage:

Syntax error converting the varchar value 'SELECT @complete = COUNT(RespondentUniqueKey) FROM cscResponse2001 INNER JOIN cMasterDemo ON cscResponse2001.ourID = cMasterDemo.[id] WHERE responseDate >= '06/01/01' AND responseDate <= '06/30/01' AND method = ' to a column of data type int.


Now I obviously just don't &quot;get&quot; what's going on here, so please help me out -- This seems so incredibly bass-ackwards to me that I'm about to just get mgmt to just hire someone who is a db admin and let me just do what I know. I'm feeling extremely inept at this point. I know it has to be something simple, but I am just flat out missing the point here.

thx -
paul

 

[tlbroadbent]

You need to convert INT values to character in the SET statement. I left that out when I said to remove the single quotes. You'll also need to convert the datetime variables.

SET @sqlStatement = 'SELECT @complete = COUNT(RespondentUniqueKey) FROM cscResponse2001 INNER JOIN cMasterDemo ON cscResponse2001.ourID = cMasterDemo.[id] WHERE responseDate >= ''06/01/01'' AND responseDate <= ''06/30/01'' AND method = ' + str(@method,4) + ' AND firstMail >= ''' + convert(char(10), @startDate,101) + ''' AND firstMail <= ''' + convert(char(10), @endDate, 101) + ''' AND accountType = ' + str(@accountType,4) + ' AND dm = ' + str(@dm,4)

I didn't notice previously the @complete variable in the Select statement. In order to return the count you should use sp_excutesql rather than Execute.

exec sp_executesql @SQLStatement,N'@complete int output', @complete output

If my brain was really working right, we could make this simpler but this will do for now. Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[link9]

Next error -- looks like it's bombing for some reason on trying to execute the statement with the sp_executesql stuff (that, by the way, I have no idea what you're doing there)

Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.

Now, I don't even have a parameter '@statement' in the SPROC, so I hope that error makes some sense to you --

Do you need more information to help out? If so, ask away... please.

And thanks so much again, Terry.

paul

 

[link9]

... anyone?

 

[tlbroadbent]

Change the declaration of @sqlstatement to nvarchar(4000). Are you sure it says @statement or is ir @sqlstatement?

Declare @sqlStatement nvarchar(4000)

BTW: You don't need to declare all the variables in the SP definition. Most can and should be declared in the body of the SP using the Declare statement. Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[link9]

Ok, I changed it to nvarchar -- missed that before... and yes, the error message that you saw above was a copy/paste job from the page that generated it -- @statement

Thanks for the tip about declaration of variables...

and now for the punchline --

It's giving me the same error message that this whole thing started with:

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.


It has something to do with when we try and insert the @startDate and @endDate -- I'm sure of it. Anywho, the columns that I'm trying to reference with the @startDate and @endDate are both of type smalldatetime...

Here's the statement again:

SET @sqlStatement = 'SELECT @complete = COUNT(RespondentUniqueKey) FROM cscResponse2001 INNER JOIN cMasterDemo ON cscResponse2001.ourID = cMasterDemo.[id] WHERE responseDate >= ''06/01/01'' AND responseDate <= ''06/30/01'' AND method = ' + str(@method,4) + ' AND firstMail >= ''' + convert(char(10), @startDate,101) + ''' AND firstMail <= ''' + convert(char(10), @endDate,101) + ''' AND accountType = ' + str(@accountType,4) + ' AND dm = ' + str(@dm,4)

And the really strange thing is that it will execute about four times before it throws this error -- it is supposed to execute about 25 times on this one page.

I hate to keep coming back and asking so many questions, but I'm really at a loss here, and your help is much appreciated.

paul

 

[tlbroadbent]

Is the value of @startdate and/or @enddate NULL? If so that is the likely problem. You either need to select a valid default value for both or change the logic to not include these dates in the criteria when the parameters are NULL. For a quick test, modify the SP to set the default values to
January 1, 1900 and June 6, 2079 respectively. These are the minimum and maximum smalldatetime values.

@startDate smalldatetime = '01/01/1900',
@endDate smalldatetime = '06/06/2079',
Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[link9]

Just wanted to update you and let you know I FINALLY figured out what the problem was, and of course, it was in the webpage --

I was calling the parameters.refresh method on the command object that was referencing the stored procedure before every call... so on a wild hair, I went through and cleaned out any unnecessary calls to .refresh leaving only the ones that had to be there and **POOF** -- bye bye error -- go figure.

Sometimes, ya know?!?!

Anyway, thanks again for you assistance.