[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 941
  • Last Modified:

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?
0
dbaSQL
Asked:
dbaSQL
  • 7
  • 5
1 Solution
 
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
 
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now