Thursday, July 24, 2008

Querying date with java and sql

Two ways to query a date:
1. by formatting and querying as a string
2. by to_date sql function

public List getPersonList(Timestamp startDate, Timestamp endDate)
{
String startDateStr = new SimpleDateFormat("yyyy-MM-dd hh24:mm:ss").format(startDate);
String endDateStr = new SimpleDateFormat("yyyy-MM-dd hh24:mm:ss").format(startDate);

List toReturn = new ArrayList();
StringBuffer personQuery = new StringBuffer();
personQuery.append("select user from User as user ");

if(startDate != null && endDate != null)
{
registrationGrowthQuery.append(" where activationSentTime >= '" + startDateStr + "' and activationSentTime <= '" + endDateStr + "'");

registrationGrowthQuery.append(" where activationSentTime >= to_date("+startDate+", 'yyyy-MM-dd hh24:mm:ss') and activationSentTime <= to_date("+endDate+", 'yyyy-MM-dd hh24:mm:ss')");
}