SQL Query to search for row value by partial string of the row value

Question

This is my first time asking a question here. Hope you guys can help. I have been trying to do a search function in my app where if for example I search 2016, all dates with the year 2016 would display on JTable from my database. This is my current code and it displays just the columns and no results on JTable:

String searchDate = dateTextField.getText();
String query = "select dateandtime,category,activity from history where dateandtime like '" + searchDate+ "'";

I tried to replace '" + searchDate+ "'" to %'2016'% and it worked. BUt I need to search for other years as well so I would need to query using the text from the textfield. How do I go about doing this so I could use the string entered on the textfield to do the search and come up with results.

WOW! Thank you so much Vincent! This worked! Sorry, I'm new to SQL, I did try to search for answers though, took me a day of searching before asking here. Thanks for the help :) — DJ Tan
– DJ Tan, Commented Oct 13, 2016 at 4:16

Prima Shella · Accepted Answer · 2016-10-13 04:17:03Z

2

you could do that by adding % before and after the quotes

String query = "select dateandtime,category,activity from history where dateandtime like '%" + searchDate+ "%'";

answered Oct 13, 2016 at 4:17

Prima Shella

931 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

@DJTan: don't use LIKE for DATEs or TIMESTAMPs

user330315 · Accepted Answer · 2016-10-13 06:06:01Z

0

LIKE is for Strings, not for dates.

When you use LIKE against a TIMESTAMP (or DATE) column the database has to convert that date value to a string - and you have no real control over the format of the date that is used for that. What's even worse: this could work differently in different environments. Most DBMS do that evil implicit data type conversion based on information from the client application, not based on the server configuration. So you might get different results depending on where the code is running.

You didn't specify your DBMS, but with standard SQL you should prefer something like this:

where extract(year from dateandtime ) = 2016

or

where dateandtime >= date '2016-01-01' 
  and dateandtime < date '2016-01-01'

The second statement has the advantage that the database can use an index on that column. The first one would require a special expression based index.

answered Oct 13, 2016 at 6:06

user330315

1 Comment

DJ Tan Over a year ago

Thanks for this. I realized that it would create problems if I query a date using String so I changed it to Date instead:

Aaron Bertrand · Accepted Answer · 2018-11-10 15:23:19Z

0

Is the dateandtime column is a DATE or TIMESTAMP type... why are you querying it by using string comparison?

See this blog for a long list of bad habits on date range queries:

Bad habits to kick : mis-handling date / range queries

edited Nov 10, 2018 at 15:23

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

answered Oct 13, 2016 at 4:56

KC Wong

2,5091 gold badge21 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL Query to search for row value by partial string of the row value

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related