使用fromdate和todate搜索查询 [英] searching query using fromdate and 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

推荐答案

首先，我不知道为什么要为各种where条件设置Begin-Set-End.所有这些都可以直接编写，因为它是一个存储过程，其中所有查询参数已经以可以直接使用的形式存在.
Null根据需要检查是否可以在业务/数据库层中更早地完成任何操作.即使在SP中，也可以一开始就完成一次.

现在，为了进行DATE比较，您也可以使用BETWEEN关键字.看看以下内容:
BETWEEN(Transact-SQL) [

First of all, I don''t know why you have Begin-Set-End for the various where conditions. All can be directly written as it''s a stored procedure where all the query parameters are already present in a form that can be used directly.
Null checks if any can be done earlier in business/database layer based on need. Even if in the SP then it can be done in one shot at the start.

Now, for DATE comparison, you can use BETWEEN keyword also. Have a look at the following:
BETWEEN (Transact-SQL)[^]

Thus, your where clause should be something like:

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

--OR 

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

只需尝试一下..

首先使用101日期格式转换日期

这是您的查询

Just try it..

First convert the date using 101 date format

This is your query

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搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！