How to search grid view using date column?

Ganesh babu

7y964

In a SQL Server database, I have a table Priority with 3 columns id, name and 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.

CREATE PROCEDURE [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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
Please refer below thread, solved your problem
IF @SearchBy = '1'
BEGIN
SELECT *FROM PRIORITY WHERE NAME LIKE  'B%' AND DATE_FROMAT BETWEEN CONVERT(varchar, 'FROMDATE', 23)  AND CONVERT(varchar, GETDATE(), 23)
END
0
Amit Kumar7y ago
Hi Ganesh, You can do it like that. You do not need to use like keyword in the case of datetime filter use for it between keyword which I am used in this store procedure use it. use this procedure it will work sure. CREATEPROCEDURE [dbo].[SEARCHPRIOR] @SearchBy VARCHAR(50), @SearchVal VARCHAR(50), ASBEGIN IF @SearchBy = '1'BEGINSELECT * FROM PRIORITY WHERE P_NAME LIKE'%' + @SearchVal + '%'ENDELSE IF @SearchBy = '2'BEGINSELECT * FROM PRIORITY WHEREconvert(varchar(10),P_TS ,126) betweenconvert(varchar(10), @SearchVal ,126) andconvert(varchar(10),@SearchVal ,126)ENDELSEBEGINSELECT * FROM PRIORITY ENDEND It will work.
0
Jignesh Kumar7y ago
Hi Ganesh,

Please find below code change for date search,
ELSE IF @SearchBy = '2'
BEGIN
SELECT * FROM PRIORITY WHERE CONVERT(Datetime, P_TS, 103) = CONVERT(Datetime,@SearchVal, 103)

END
0
Amit Gupta7y ago
Don't use like when searching date. Use BETWEEN or >= <=
0