Link to home
Start Free TrialLog in
Avatar of crich
crich

asked on

Sending Greater Than and Less than with Date Parameters

Hi all,
 I am trying to construct a parametersed query. I have constructed a stored prodedure and an asp.net class which seems to be doing everything i need. the only last problem i have is with the dates. This is the stored procedure:

ALTER PROCEDURE [dbo].[SelectApplications] (@titleFilter  varchar(50),@appType varchar(3), @programme varchar(3),@status varchar(3), @startDate varchar(15),@endDate varchar(15),@department varchar(30))
AS
declare @sql varchar(2000)
BEGIN
set @sql = 'select top 100 * from applications where
app_type like ''' + @appType  + ''' and app_title1 like ''%' + @titleFilter + '%'' and
progid like  ''' + @programme + ''' and
app_status like ''' + @status + ''' and
startDate ' + @startDate + ' + and
endDate + ' + @endDate + ' +  and
DeptId like ''' + @department + ''' '
exec (@sql)
END

My asp.net class sends the following code to the stored procedure:
exec SelectApplicationsView @titleFilter='',@appType='%',@programme='%',@Status='%',@startDate='< 1/1/2000',@endDate='> 1/1/1900',@department='%'

as you can hopefully see, my problem is that the dates are being put in single quotes when in actual fact they shouldn't be in single quotes.

do you know how to acheive this in dynamic sql?
or should i be doing this query another way?

thanks!
crich


Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

use BETWEEN
ASKER CERTIFIED SOLUTION
Avatar of Yveau
Yveau
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of crich
crich

ASKER

hi thanks for the replies. i will have to give points to Yveau as the solution built upon my code even though i expect i could have tried between. i hope this is ok...

Thanks a lot for your help, it worked fine! I had to add some convert statements too..

richard