You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by jill han <jh...@bynum.com> on 2005/11/09 17:15:01 UTC
how to write a query with Date type
Below is a piece of code to get records from a table and exception
// startDate and endDate are Date object
// aDate has Date data type in oracle db
String sql = "Select * from aTable ";
sql = sql + " Where aDate ";
sql = sql + " Between '" + startDate + "' And '" + endDate + "' ";
List sqlResult = aTablePeer.executeQuery(sql);
org.apache.torque.TorqueException: ORA-01858: a non-numeric character
was found where a numeric was expected
Thanks in advance.
Jill
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: how to write a query with Date type
Posted by Dave Newton <ne...@pingsite.com>.
jill han wrote:
>Below is a piece of code to get records from a table and exception
>// startDate and endDate are Date object
>// aDate has Date data type in oracle db
>String sql = "Select * from aTable ";
>sql = sql + " Where aDate ";
>sql = sql + " Between '" + startDate + "' And '" + endDate + "' ";
>List sqlResult = aTablePeer.executeQuery(sql);
>
>org.apache.torque.TorqueException: ORA-01858: a non-numeric character
>was found where a numeric was expected
>
>
I'm assuming you're asking why this didn't work.
java.util.Date's toString returns something along the following lines:
Wed Nov 09 11:19:05 GMT-05:00 2005
which isn't what an SQL date looks like (although I don't deal with
Oracle much, so I could be completely wrong).
You'll want to convert it to a format that Oracle understands.
I thought the Criteria date stuff would do that work for you (I don't
know that either, though ) If that's true, is there a reason to avoid
Criteria for this usecase?
Dave
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: how to write a query with Date type
Posted by econroy <ec...@ECONROY.NET>.
jill han wrote:
>Below is a piece of code to get records from a table and exception
>// startDate and endDate are Date object
>// aDate has Date data type in oracle db
>String sql = "Select * from aTable ";
>sql = sql + " Where aDate ";
>sql = sql + " Between '" + startDate + "' And '" + endDate + "' ";
>List sqlResult = aTablePeer.executeQuery(sql);
>
>org.apache.torque.TorqueException: ORA-01858: a non-numeric character
>was found where a numeric was expected
>
>
>Thanks in advance.
>
>Jill
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>For additional commands, e-mail: torque-user-help@db.apache.org
>
>
>
>
>
Using Criteria and calender can solve this problem.
This is sort of the type of code you would use:
GregorianCalendar calendar = new GregorianCalendar();
// this normalizies the dates
calendar.setTime(startDate);
calendar.setTime(endDate);
Criteria sql = new Criteria();
sql.add(aDate, startDate, Criteria.GREATER_EQUAL);
Criteria.Criterion criterion = sql.getCriterion(aDate);
criterion.and(sql.getNewCriterion(criterion.getTable(),
criterion.getColumn(), endDate, Criteria.LESS_EQUAL));
try
{
List list = aTablePeer.doSelect(sql);
}
catch(Exceptiion e)
{}
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org