Stored Proc Date conversion Problem, Please Help 
Author Message
 Stored Proc Date conversion Problem, Please Help

I'm using Visual Interdev to create a SQLS 7.0 stored procedure that selects
some records from a couple of tables based on a date typed by the user in
the format mm/dd/yy.  The stored procedure looks like this

If Exists (Select * From sysobjects Where name = N'NewsByDate' And
user_name(uid) = N'dbo')
 Drop Procedure dbo.NewsByDate
Go

CREATE Procedure NewsByDate

 (

 )

As

 SELECT T_Company.CompanyName, T_CompanyNews.NewsDate,
 T_CompanyNews.NewsHeadline, T_CompanyNews.NewsStory
 FROM T_Company INNER JOIN T_CompanyNews
 ON T_Company.CompanyID = T_CompanyNews.CompanyID

 ORDER BY T_CompanyNews.NewsDate
 return

Go



Syntax error converting character string to smalldatetime data type.
Syntax error converting character string to smalldatetime data type.
Finished running dbo."NewsByDate".

Is there something I need to do to make the conversion from text to string
take place?

Thanks in advance,

Bill



Mon, 09 Dec 2002 03:00:00 GMT
 Stored Proc Date conversion Problem, Please Help

Hi Bill,

You're problem is in the query, you use the sequence:-


in the WHERE clause, This is attempting to concatenate two strings with a
date!

IF the T_CompanyNews.NewsDate  is a date data type just use a straight
coparison:-


If the T_CompanyNews.NewsDate  field is a charater field, convert the date
to a string first:-


+ '%'

check the value of the thrid argument of CONVERT as this controls the date
format generated during conversion.

Hope this helps

MArtin


Quote:
> I'm using Visual Interdev to create a SQLS 7.0 stored procedure that
selects
> some records from a couple of tables based on a date typed by the user in
> the format mm/dd/yy.  The stored procedure looks like this

> If Exists (Select * From sysobjects Where name = N'NewsByDate' And
> user_name(uid) = N'dbo')
>  Drop Procedure dbo.NewsByDate
> Go

> CREATE Procedure NewsByDate

>  (

>  )

> As

>  SELECT T_Company.CompanyName, T_CompanyNews.NewsDate,
>  T_CompanyNews.NewsHeadline, T_CompanyNews.NewsStory
>  FROM T_Company INNER JOIN T_CompanyNews
>  ON T_Company.CompanyID = T_CompanyNews.CompanyID

>  ORDER BY T_CompanyNews.NewsDate
>  return

> Go

> When I test the SP by Executing it in VID, I'm prompted for a value for :


> Syntax error converting character string to smalldatetime data type.
> Syntax error converting character string to smalldatetime data type.
> Finished running dbo."NewsByDate".

> Is there something I need to do to make the conversion from text to string
> take place?

> Thanks in advance,

> Bill



Mon, 09 Dec 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Extended Stored Proc dll - reading SRVDATETIMN - conversion to what Date Type

2. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

3. DATE CONVERSION HELP PLEASE

4. Date Conversion Please Help

5. please help with date conversion error 4 collection.

6. character-2-ascii conversion please please please help !

7. Date problems between SQL-Server 7.0 Stored Proc and VB.

8. stored proc parameter problem, comments please

9. -----need help with stored proc---PLEASE

10. PLEASE HELP - Dynamic SQL in a stored proc

11. Query/View/Stored Proc structure - please help!?

12. Please Help, Nested stored proc is inconsitent


 
Powered by phpBB® Forum Software