Problem with String to Date Conversion

I am trying to convert date in String format to sql date and based on that query database to get the result. Date in string format is :2011-08-11 09:16:00.0 So I am converting it to sql date by using the method:

public static convertStringToSqlDate(String dateString){
    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
    java.ut开发者_Go百科il.Date parsedUtilDate = formater.parse(dateString);
    java.sql.Date sqlDate= new java.sql.Date(parsedUtilDate.getTime());
    return sqlDate;
}

The resulting date is :2011-08-11

but while doing the query I am not getting desired output

The complete code is

def startDate = params. startDate
def endDate = params. endDate
def formattedTripStartDate  =Trip.convertStringToSqlDate(startDate);
def formattedTripEndDate    =Trip.convertStringToSqlDte(endDate);
def listOfContracts = Rule.findAll("FROM Rule WHERE name LIKE ? AND client_id = ? AND STR_TO_DATE(contract_begins,'%Y-%m-%d')<= ? AND STR_TO_DATE(contract_terminates,'%Y-%m-%d')>= ?",["%"+q_param+"%",clientId,formattedTripStartDate,formattedTripEndDate] )

Where am I going wrong?

In database the contract_begins is stored as :2011-08-23 00:00:00

Contract domain Class is

class Contract extends Rule {

Date    contractBegins
Date    contractTerminates
int     runningDays
Double  contractValue
Double  estimatedRevenue
Double  actualRevenue
static constraints = {
    contractBegins(nullable:true)
    contractTerminates(nullable:true)
    runningDays(nullable:true)
    contractValue(nullable:true)
    estimatedRevenue(nullable:true)
    actualRevenue(nullable:true)
}
  }

Date object didn't format by itself, it return only date and time value. you can get the formatted by only Format supported class in string format like SimpleDateFormat, DateFormat etc.

Why do you want to use a findAll query, and not a criteria. Something like this should do it:

def startDate = params.startDate
def endDate = params.endDate
def tripStartDate=Trip.convertStringToSqlDate(startDate);
def eripEndDate=Trip.convertStringToSqlDte(endDate);

def c = Contract.createCriteria()
def results = c.list {
    like("name", "%${q_param}%")
    eq("client_id", clientId) 
    ge('contract_begins', tripStartDate)
    le('contract_terminates','tripEndDate) 
}

It's much cleaner and you don't have to worry about how the SQL looks like!

Take a deep dive into http://grails.org/doc/latest/ref/Domain%20Classes/createCriteria.html and http://grails.org/doc/latest/guide/5.%20Object%20Relational%20Mapping%20(GORM).html#5.4.2 Criteria where you can find much more information.

Also consider making your code even nicer by adding the criteria into the Domain Class named queries: http://grails.org/doc/latest/ref/Domain%20Classes/namedQueries.html