2

The records in the db look like this:

10//12/2013
10/13/2013
10/16/2013
10/20/2013

I want to search 2013 in the above records. Do i have to split first above values?

from CustomerRelations where DATE like'" + input + "'

Can I use like here?

Or how else can I do this?

   Query q = session.createQuery("from CustomerPayment where DATE like '" + code + "'");

The Above code does not work.

Steven
  • 1,365
  • 2
  • 13
  • 28
user2326472
  • 31
  • 1
  • 6
  • U stored them as datetime, not a string I might hope ? – DarkBee Apr 29 '13 at 10:57
  • i stored them as a varchar – user2326472 Apr 29 '13 at 10:58
  • If u want to search/calculate/do anything with dates as a date be sure to store them as a date or timestamp in your database. Will make your life much easier [Getting values out of the DB](http://stackoverflow.com/questions/7875196/mysql-datetime-not-returning-time) and [between date1 and date2](http://www.techonthenet.com/sql/between.php) – DarkBee Apr 29 '13 at 11:01

3 Answers3

2

Since you've saved the dates as VARCHAR do a wildcard String match

Query q = session.createQuery("from CustomerPayment where DATE like '%" + code + "'");

LIKE clause lets you use two wilcards:

  • _ = matches just one single character
  • % = matches any number of characters

Ideally, you should have created your table with a TIMESTAMP column. That would allow you to compare dates accurately and build queries like "fetch all customer payments in the last three months" etc.

Ravi K Thapliyal
  • 51,095
  • 9
  • 76
  • 89
1

you can use 

datepart(year,datefield)

where datefield is your field from which you want to retrieve the year

Query q = session.createQuery("SELECT date FROM CustomerPayment WHERE datepart(year,"+Convert.ToDateTime(datefield)+")="+ input+";
Anuj
  • 1,496
  • 1
  • 18
  • 28
0

Try changing your query to

Query q = session.createQuery("SELECT date FROM CustomerPayment WHERE date LIKE '%"+input+"'"

since your date field is a string, you must do the lookup via a string compare

Steven
  • 1,365
  • 2
  • 13
  • 28