syntax error converting char string to smalldatetime

this is my error:  
Server: Msg 295, Level 16, State 3, Procedure, procedurename, Line 28
Syntax error converting character string to smalldatetime data type.

this is my proc:


IF OBJECT_ID('procedurename','p') IS NOT NULL
DROP PROC procedurename
GO
CREATE PROCEDURE procedurename (
 @topcount int,
 @tsdate smalldatetime=NULL,
 @latencytype char(2)=NULL,
 @msgsource char(1)=NULL
)
AS
SET NOCOUNT ON


IF(@tsdate IS NULL)
BEGIN
  --if the parm is passed null, just get the max value (should use idx effectively)
  SELECT @tsdate = MAX(tsdate)FROM database.dbo.maintable
END
  --remove the time portion
--  SET @tsdate = CONVERT(SMALLDATETIME,CONVERT(VARCHAR(20),@tsdate,120),120)
  SELECT @tsdate = CONVERT(VARCHAR(10),@tsdate,120)


 DECLARE @SQL VARCHAR(5)
  SET @SQL = 'SELECT TOP '+ CAST(@topcount AS VARCHAR)  
 ' OrderNo,MsgSource,Latency,LatencyType,tsdate
FROM database.dbo.maintable WITH (NOLOCK)
WHERE ('+@tsdate+' IS NULL OR tsdate = '+@tsdate+')
AND ('+@latencytype+' IS NULL OR LatencyType = '+@latencytype+')
AND ('+@msgsource+' IS NULL OR MsgSource='+@MsgSource+')
ORDER BY LatencyType,Latency DESC '

PRINT(@SQL)
--EXEC(@SQL)
GO

I am embarrassed that I haven't found it yet.  Does anybody see it?
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
IF OBJECT_ID('procedurename','p') IS NOT NULL
DROP PROC procedurename
GO
CREATE PROCEDURE procedurename (
 @topcount int,
 @tsdate smalldatetime=NULL,
 @latencytype char(2)=NULL,
 @msgsource char(1)=NULL
)
AS
SET NOCOUNT ON 

IF(@tsdate IS NULL)
BEGIN
  --if the parm is passed null, just get the max value (should use idx effectively)
  SELECT @tsdate = MAX(tsdate)FROM database.dbo.maintable
END 
 DECLARE @SQL VARCHAR(5000)
  SET @SQL = 'SELECT TOP '+ CAST(@topcount AS VARCHAR)  
 ' OrderNo,MsgSource,Latency,LatencyType,tsdate 
  FROM database.dbo.maintable WITH (NOLOCK)
  WHERE tsdate >= CONVERT(datetime, '''+ CONVERT(varchar(10), @tsdate, 120) +''', 120 )
  AND  tsdate < DATEADD(day, 1, CONVERT(datetime, '''+ CONVERT(varchar(10), @tsdate, 120) +''', 120 ))
  ' + CASE WHEN @latencytype IS NULL THEN '' ELSE ' AND LatencyType = '''+@latencytype+''' ' END 
    + CASE WHEN @msgsource IS NULL THEN '' ELSE ' AND MsgSource='''+@MsgSource+''' ' END 
   + ' ORDER BY LatencyType,Latency DESC ' 
PRINT(@SQL)
--EXEC(@SQL)
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
yup, angelll, it works perfectly.  can you point out my error, please?  (though i'm sure it is obvious  :-)   )
also, can you clarify a little on the time parm?  how you're stripping out the timestampd from the given or default @tsdate ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you had several errors...

but the main error is that you need to do the double conversion, the variable of data type varchar to concatenate it to the sql string in a well-know format, and inside the sql string, to convert it back to a datetime value.

next:
WHERE ('+@tsdate+' IS NULL OR tsdate = '+@tsdate+')
would make the entire sql string back to NULL if @tsdate were null

another:
DECLARE @SQL VARCHAR(5)
would limit the sql varchar to 5 characters ...  :)



0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dbaSQLAuthor Commented:
eeewgh... i did say 'embarrassed'....
thank you, angelll
much appreciated
0
dbaSQLAuthor Commented:
Thanks very much for your time.
0
dbaSQLAuthor Commented:
hey angellll, just real quick -- is there any way to do the top count without using dynamic?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sql 2005? yes,
SELECT TOP @variable ....

sql 2000? yes

SET ROWCOUNT @variable
SELECT ...
0
dbaSQLAuthor Commented:
yep, yep, i tried the rowcount earlier, but failed syntactically.  i will have to try it again, as i just don't feel so good about they dynamic.  a., security.  b., doesn't the optimizer have a hard time w/dynamic?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, you might be using sql 7 or a sql 2000 with compatibility 70 set?
0
dbaSQLAuthor Commented:
i'm on v2000.  what do you mean the '70 set', angellll?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
dbaSQLAuthor Commented:
aah yes.... i'm not at 70.  my problem was simply syntax.  i've got it now, am testing performance.
some are great, some are terrible
cross your fingers, i've got to figure this one out
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.