How to search grid view using date column?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2014

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

Posted 26-Apr-19 0:15am

Member 14185275

Updated 26-Apr-19 4:02am

v3

Add a Solution

Comments

Animesh Datta 26-Apr-19 9:49am

what the datetime format you are passing in @SearchVal when @SearchBy = '2'

Member 14185275 26-Apr-19 9:55am

datetime format passing as input is yyyy-mm-dd

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Animesh Datta · Accepted Answer · 2019-04-26T04:03:00

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 + '%'

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