Hello people, I have an strange problem here.
Suppose I have a table mytable with a column mydate of type date.
if I launch this SQL directly with psql:
SELECT date_trunc('month', mydate)::date FROM mytable WHERE
mytable.mydate BETWEEN '2000-07-25 +02:00' AND '2004-07-25 +02:00' GROUP
BY date_trunc('month', mydate);
I obtaint the months/years in where exists any record in this table with
a date stored in mydate that uses this month/year, all between 2 dates
This sql works ok under psql. But if I try to execute it with jdbc:
String sql = "SELECT date_trunc('month', mydate)::date FROM mytable
WHERE mytable.mydate BETWEEN ? AND ? GROUP BY date_trunc('month', mydate)";
PreparedStatement pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Calendar calendar = Calendar.getInstance();
//initial date, suppose f_ini has the correct date
calendar.setTime(f_ini);
pstmt.setDate(1, new java.sql.Date(calendar.getTimeInMillis()));
//end date, suppose f_end has the correct date
calendar.setTime(f_end);
pstmt.setDate(2, new java.sql.Date(calendar.getTimeInMillis()));
pstmt.executeQuery();
The code works ok, but when it reaches to executeQuery() statement it
fails with message "mytable.mydate must be used in group by clause or in
aggregate function"
Where is the fault?.
I'm using postgresql 8.1.9 and jdbc driver 8.1 build 409 JDBC3
Best regards