CODEWITHSUNDEEP
sql
Sachin HR
Sachin HR

Reputation: 460

Query For Dates Based on start date and end date

I have two datepickers from and to dates.And then one textbox.After selecting dates and typing some name in text box I need to filter records in table.I have Admission date in my table.I need to write query so that if the Student name matches and also if admissionDate falls between fromdate and todate then it should display the record

I am writing this query but it is not working.

SELECT studentId, studentName, admissionDate
from Student
where studentName like :studentName
  AND admissionDate BETWEEN (fromDate like :fromDate AND toDate like :toDate)
order by studentId desc

From DaoImpl I am getting fromDate and toDate values

DaoImpl

    @Override
    public List<Student> getSearchResultsByKeyword(String search,Date
    fromDate,Date toDate) {
    List<Student> users = new ArrayList<Student>();
    System.out.println("Student Name:"+search+"fromDate:"+fromDate);
    Query query = 
    sessionFactory.getCurrentSession().createSQLQuery(SEARCHSTUDENTS);
    query.setParameter("studentName", "%"+search+"%");
    query.setParameter("fromDate", fromDate);
    query.setParameter("toDate", toDate);
    /*query.setParameter("admissionDate","%"+search+"%");
    query.setParameter("rollNumber","%"+search+"%");
    query.setParameter("gender","%"+search+"%");*/
    query.setResultTransformer
    (Transformers.aliasToBean(Student.class));
    users = query.list();
    return users;
    }

Upvotes: 0

Views: 85

Answers (1)

MirzaS
MirzaS

Reputation: 512

Try without using between like this

SELECT studentId, studentName, admissionDate
from Student
where studentName like :studentName
  AND admissionDate >= :fromDate AND admissionDate <= :toDate
order by studentId desc

And make sure that date formats you are sending are the same as DB date format (e.g YYYY/MM/DD)

Upvotes: 1

Related Questions