Hi everyone,
One of the things that I struggled in the beginning (a long time ago in a galaxy far away, cof cof) with SQL queries (or BOQL queries if you’re using the XEO framework) was how to make queries using dates in the where clause, something like this:

select * from table where column_date > SOMETHING

Not knowing how to do date comparisons I usually did stuff like this ( evil code ahead, beware ) :

select * from some_table where to_char(column_data,'DD-MON-YYYY') > to_char('23-05-2013','DD-MON-YYYY');

Usually in Java you have java.util.Date instances which I heroically used like the following, in order to be able to create the previous query:

SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
java.util.Date date = loadDateFromSomewhere();
String sql = "select * from some_table where to_char(column_data,'DD-MON-YYYY') > ('"+sdf.format(date)+"','DD-MON-YYYY')";

Well now I don’t do this kind of thing, eh eh… it’s way easier to do such queries using proper data types. Check in a Java SQL Prepared statement:

Connection conn = getConnection();
PreparedStatement statement = conn.prepareStatement("select * from some_table where column_date > ?");
statement.setDate(1, new java.sql.Date(loadDateFromSomewhere().getTime()));

Notice how you pass a java.sql.Date instance (created using the information from the java.util.Date instance) to the parameter. This has the added benefit that no data conversions are done (meaning extra performance). And if you have an index on a date column that index will be used ( I also learned that when you apply a function to an indexed column, that index won’t be used properly as the function has to be applied to every row in the table – check this )

In BOQL by the way it would be like this:

boObjectList.list(getEboContext(), "select Object where column_data > ?", new Object[]{ new java.sql.Date(date.getTime()) });

Important notes:

• In SQL/BOQL queries you use java.sql.Date and java.sql.Timestamp instances (you don’t use java.util.Date instances)
• Use java.sql.Date instances when you want to compare Dates only (ignoring time)
• Use java.sql.Timestamp instances when you want to compare Dates including time information

And voilá… Dates in SQL/BOQL queries (using Java) without much effort. Hope it was useful,  Happy coding!