使用fromdate和todate搜索查询

问题描述

Hai Friends，

我对serching(state，district，mandal，crops，username和fromdate，todate)的要求很高..

我写这样的查询，它的工作到用户名，但我选择fromdate，至今它不工作是什么..

请帮助我....

我的查询是

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

希望有人帮忙...
谢谢
Venkat.S

i hope somebody help...
Thank
Venkat.S

推荐答案

SELECT * FROM MyTable WHERE DBCheckDate BETWEEN @FromDate AND @ToDate

--OR 

SELECT * FROM MyTable WHERE DBCheckDate > @FromDate AND DBCheckDate < @ToDate 

set @query=@query+ '' and  CAST(casestudy.dtConducteddate AS DATE)  < ''+ Convert (varchar(10),CAST(@todate AS DATE))

试试这个

Try this

set @query=@query+ '' and  CAST(casestudy.dtConducteddate AS DATE)  < ''+ Convert(varchar(10),@todate,101)

我希望这可以.

I hope this will work.

这篇关于使用fromdate和todate搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！