Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community ForumsDatabasesMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old October 5th, 2004, 07:19 PM
adamtibballs adamtibballs is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2004
Posts: 2 adamtibballs User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Exclamation Problem searching for a date using stored procedure

Hi, I am having problem with a stored proc. Basically im building a string to run a search
on the database depending on what parameters have had values passed to them.
The problem is that the code regarding the date throws this error -

Microsoft OLE DB Provider for SQL Servererror '80040e07'
Syntax error converting datetime from character string.

here's the stored procedure code (problem code is in red)-

Code:
 
CREATE PROCEDURE [dbo].[usp_Forms_GetSearchResults] 
@surname varchar(100),
@outletid int,
@formtype int,
@lodgementdate datetime,
@assignedto varchar(50),
@formstatus varchar(10),
@userID varchar(8)
AS
declare @sqlstr varchar(1999) 
select @sqlstr = 'Select * From tblFormsManagement where 1=1'
if @surname<>''
begin
if patindex('%*%',@surname)>0
select @sqlstr = @sqlstr + ' and tblFormsManagement.ClientLName like ''' + replace(@surname,'*','') + '%'''
else
select @sqlstr = @sqlstr + ' and tblFormsManagement.ClientLName = ''' + @surname + ''''
end
if @outletid<>0
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.OutletId = ' + convert(varchar,@outletid) 
end
if @formtype <> 0
begin
if @formtype =1
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ClientIntakeForm"'
	end
if @formtype =2
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ClientExitForm"'
	end 
if @formtype =3
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "DPI"'
	end
if @formtype =4
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "13Week"'
	end
if @formtype =5
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "DMICaseBase"'
	end
if @formtype =6
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ClientSuspensionForm"'
	end
if @formtype =7
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ReturnfromSuspensionForm"'
	end
if @formtype =8
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "ClientDetailsForm"'
	end
if @formtype =9
	begin
	 select @sqlstr = @sqlstr + ' and tblFormsManagement.FormType = "HighCost"'
	end
end
if @lodgementdate <> null
begin
if @lodgementdate<>''
begin
	select @sqlstr = @sqlstr + ' and tblFormsManagement.CreatedDate = ' + @lodgementdate
end
end
if @assignedto <> ""
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.AssignedTo = ''' + @assignedto + ''''
end
if @formstatus <> ""
begin
select @sqlstr = @sqlstr + ' and tblFormsManagement.FormStatus = ''' + @formstatus + ''''
end
execute (@sqlstr + ' Order by OutletName')
GO


Any help would be great. Im sure its something simple but I don't have enough experience with
stored procs to figure it out myself and I haven't been able to find anything on the net to help.

Thanks

Reply With Quote
  #2  
Old October 8th, 2004, 09:32 AM
vikramp vikramp is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2004
Posts: 7 vikramp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Hi adamtibballs,


There are couple of things:
First of all, @lodgementdate is of type datetime and cannot be concatinated with the rest of the string directly. You need
to convert it to varchar.
Secondly, while contactinating, your converted date should appear in quotes(').

So your statement would be like this:

select @sqlstr = @sqlstr + ' and tblFormsManagement.CreatedDate = ''' + CONVERT(varchar(20), @lodgementdate) + ''''


Hope this solves the problem.

Regards,

Vikram

Reply With Quote
  #3  
Old October 10th, 2004, 09:36 PM
adamtibballs adamtibballs is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2004
Posts: 2 adamtibballs User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Thanks Vikram! All working now.


Quote:
Originally Posted by vikramp
Hi adamtibballs,


There are couple of things:
First of all, @lodgementdate is of type datetime and cannot be concatinated with the rest of the string directly. You need
to convert it to varchar.
Secondly, while contactinating, your converted date should appear in quotes(').

So your statement would be like this:

select @sqlstr = @sqlstr + ' and tblFormsManagement.CreatedDate = ''' + CONVERT(varchar(20), @lodgementdate) + ''''


Hope this solves the problem.

Regards,

Vikram

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft SQL Server > Problem searching for a date using stored procedure


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway
Stay green...Green IT