Searching query using fromdate and todate
Hai Friends,
i have requirement like serching (state,district,mandal,crops,username,and fromdate ,todate) wise..
i write query like this its working upto usernames but i selct fromdate and todate its not working whats wrong this ..
plz help me....
My query is
ALTER Proc [dbo].[sp_SearchAllReports1]
-- sp_SearchAllReports1 null,null,null,null,null,'10/11/2001'
(
@intStateId int =null,
@intDistrictId int=null,
@intMandalId int=null,
@CropsList varchar(50) =null,
@varCreatedBy varchar(50)=null,
@fromdate date=null,
@todate date=null
)
as
Begin
declare @query varchar (max)
set @query='SELECT tabProgram.varProgramCode, dbo.tabDistricts.varDistrictName, dbo.tabstates.varstatename, dbo.tabMandal.varMandalName,dbo.tabProgram.varVillageName, dbo.tabProgram.intStateId, dbo.tabProgram.intDistrictId, dbo.tabProgram.intFarmers, dbo.tabProgram.intMandalId,dbo.tabProgram.ProgSponseredBy, dbo.tabProgram.DateOfConducting,
dbo.tabProgram.intProgramId AS Expr1,
dbo.tabProgram.CropsList, tabProgram.varVillageName ,
tabMandal.varMandalName,
tabDistricts.varDistrictName
,CONVERT(varchar(20),casestudy.dtConducteddate,103)as dtConducteddate,
dbo.casestudy.* FROM dbo.tabProgram
INNER JOIN dbo.tabMandal
ON
dbo.tabProgram.intMandalId = dbo.tabMandal.intMandalId INNER JOIN dbo.tabDistricts ON dbo.tabProgram.intDistrictId = dbo.tabDistricts.intDistrictId INNER JOIN dbo.tabstates ON dbo.tabDistricts.intstateid = dbo.tabstates.intstateid INNER JOIN dbo.casestudy ON dbo.tabProgram.intProgramId = dbo.casestudy.intProgramId where 1=1'
if (@intStateId <> '0')
begin
set @query=@query+ ' and tabProgram.intStateId ='+convert (varchar(10),@intStateId)
end
if (@intDistrictId <> '0')
begin
set @query=@query+ ' and tabProgram.intDistrictId ='+convert (varchar(10),@intDistrictId)
end
if (@intMandalId <> '0')
begin
set @query=@query+ ' and tabProgram.intMandalId ='+convert (varchar(10),@intMandalId)
end
if @CropsList is not null
begin
set @query=@query+ ' and tabProgram.CropsList like'+ '%'+@CropsList+'%'
end
if @varCreatedBy is not null
begin
set @query=@query+ ' and tabProgram.varCreatedBy ='+@varCreatedBy
end
if @fromdate is not null
begin
set @query=@query+ 'and CAST(casestudy.dtConducteddate AS DATE) < '+ Convert (varchar(10),CAST(@fromdate AS DATE))
end
if @todate is not null
begin
set @query=@query+ ' and CAST(casestudy.dtConducteddate AS DATE) < '+ Convert (varchar(10),CAST(@todate AS DATE))
end
exec (@query)
end
i hope somebody help...
Thank
Venkat.S