Search for a record by date in SQL Server-CodePudding

I'm trying to make a textbox which whenever a textchanged event happens, the function will use the textbox text to search through the database to find the right records.

For example: I have 3 rows (name, phone number, birthday - the format is year - month - day - in SQL Server):

Name	PhoneNumber	Birthday
John	482	2000-7-9
Dennis	912	2001-12-9
Mike	123	2000-4-1

If the textbox.text is 9 or 9/, I want to return the 2 rows for John and Dennis.

Name	PhoneNumber	Birthday
John	482	2000-7-9
Dennis	912	2001-12-9

It is easy to search if I enter the date with yyyy-MM-dd format to the textbox, the query will be:

 SELECT * 
 FROM   database 
 WHERE  birthday LIKE %textbox.text%

I tried and it worked perfectly, but only when the date in textbox is following the yyyy-MM-day format. Is there anyway for it to work with the dd/month/yyyy format?

CodePudding user response：

The following queries show how to search using different formatting options for the value given as a 'date'.
You could construct your query with several conditions WHERE ... OR ... to match all cases.
I've changed the name of the table because database is a reserved word.
NB you should not construct your queries directly, to avoid SQL injection. Look up the safe way to do it for the language you are using.

declare @textboxtext VARCHAR(25);
set @textboxtext = '/9'
select * 
from data_base 
where day(birthday) 
LIKE replace(@textboxtext,'/','');
GO

name   |  id | birthday  
:----- | --: | :---------
John   | 482 | 2000-07-09
Dennis | 912 | 2001-12-09

declare @textboxtext VARCHAR(25);
set @textboxtext = '09/07/2000'
select * 
from data_base 
where birthday 
 = convert(date,@textboxtext,103);
GO

name |  id | birthday  
:--- | --: | :---------
John | 482 | 2000-07-09

declare @textboxtext VARCHAR(25);
set @textboxtext = '07/09/2000'
select * 
from data_base 
where birthday 
 = convert(date,@textboxtext,101) ;
GO

name |  id | birthday  
:--- | --: | :---------
John | 482 | 2000-07-09

db<>fiddle here

CodePudding user response：

Following your comment this should meet your need. NB the months must be double digit: 9/1 will match 9/11 but not 9/01

CREATE TABLE data_base (
    name VARCHAR (25),
    id int,
    birthday date);
GO

✓

insert into data_base values
('John', 482, '2000-7-9'),
('Dennis', 912, '2001-12-9'),
('Mike', 123, '2000-4-1');
GO

3 rows affected

declare @textboxtext VARCHAR(25);
set @textboxtext = '09/07/2000'
select * 
from data_base 
where 
convert(varchar(10),birthday,103) 
like CONCAT (@textboxtext,'%');
GO

name |  id | birthday  
:--- | --: | :---------
John | 482 | 2000-07-09

declare @textboxtext VARCHAR(25);
set @textboxtext = '9/'
select * 
from data_base 
where 
convert(varchar(10),birthday,103) 
like CONCAT ('%',@textboxtext,'%');
GO

name   |  id | birthday  
:----- | --: | :---------
John   | 482 | 2000-07-09
Dennis | 912 | 2001-12-09

db<>fiddle here