如何使用日期列搜索网格视图？ [英] How to search grid view using date column?

问题描述

在sql数据库表中，我有一个表名Priority.In那3列，id，name，注册日期。对于注册日期，默认值是getdate（），它自动生成记录创建的日期和时间。现在在网格中查看，我想使用此注册日期搜索记录。在网格视图中，我通过使用名称和日期进行下拉搜索。使用此查询搜索时，我没有找到数据，如果我搜索日期但我得到正确的数据如果我用名字搜索。



我尝试过：

In sql database table, i have one table name Priority.In that 3 columns, id , name, registered date.For registered date , default value is getdate() which automatically generates the record created date and time.Now in grid view, i want to search records using this registered date.In grid view, i have dropdown search by using name and date.While searching with this query, i'm getting no data found ,if i search with date but i getting correct data if i search with name.

What I have tried:

CREATE PROC [dbo].[SEARCHPRIOR]
@SearchBy   varchar(50),
@SearchVal  varchar(50),

AS
BEGIN
IF @SearchBy = '1'
           BEGIN
                SELECT * FROM PRIORITY WHERE P_NAME like '%' + @SearchVal + '%'
          END

  ELSE IF @SearchBy = '2'
          BEGIN
                SELECT * FROM PRIORITY WHERE P_TS like '%' + @SearchVal + '%'
          END

ELSE
        BEGIN
                SELECT * FROM PRIORITY 
        END
END

I have tried searching with this query but getting no results found in grid view. This is how table look like in SQL Server:

P_ID     P_NAME    P_TS
-------------------------------------------
1        High      2019-04-23 16:17:08.097
2        Medium    2019-04-24 16:17:23.070                                                               
3        Low       2019-04-25 16:17:32.420

推荐答案

你好，

我建议你像这样将 @P_TS 值作为日期格式投射

Hello ,
I would suggest you to cast the @P_TS value as date format like this way

SELECT * FROM PRIORITY WHERE 
FORMAT(P_TS, 'yyyy-MM-dd') like '%' + @SearchVal + '%' 
//FORMAT is introduced in SQL Server 2012. 

或者您也可以使用转换函数

Or you may used Convert function as well

SELECT * FROM PRIORITY WHERE 
convert(varchar, P_TS, 126) like '%' + @SearchVal + '%' 

这里我假设您以'yyyy-MM-dd'格式传递@SearchVal。

希望它可能有帮助你。

谢谢

Here I am assuming you are passing @SearchVal in 'yyyy-MM-dd' format.
Hope it may helps you.
Thanks

这篇关于如何使用日期列搜索网格视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！