{"id":272,"date":"2013-08-25T21:26:48","date_gmt":"2013-08-25T21:26:48","guid":{"rendered":"http:\/\/www.corrspt.com\/blog\/?p=272"},"modified":"2013-08-25T21:28:13","modified_gmt":"2013-08-25T21:28:13","slug":"query-sqlboql-dates-clause","status":"publish","type":"post","link":"http:\/\/www.corrspt.com\/blog\/2013\/08\/25\/query-sqlboql-dates-clause\/","title":{"rendered":"Query SQL \/ BOQL with Dates in where clause using Java"},"content":{"rendered":"

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

select * from table where column_date > SOMETHING<\/pre>\n
Not knowing how to do date comparisons I usually did stuff like this ( evil code ahead, beware ) :<\/p>\n
select * from some_table where to_char(column_data,'DD-MON-YYYY') > to_char('23-05-2013','DD-MON-YYYY');<\/pre>\n
Usually in Java you have java.util.Date<\/em> instances which I heroically<\/em> used like the following, in order to be able to create the previous query:<\/p>\n
SimpleDateFormat sdf = new SimpleDateFormat(\"dd-MM-yyyy\");\r\njava.util.Date date = loadDateFromSomewhere();\r\nString sql = \"select * from some_table where to_char(column_data,'DD-MON-YYYY') > ('\"+sdf.format(date)+\"','DD-MON-YYYY')\";<\/pre>\n
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:<\/p>\n
Connection conn = getConnection();\r\nPreparedStatement statement = conn.prepareStatement(\"select * from some_table where column_date > ?\");\r\nstatement.setDate(1, new java.sql.Date(loadDateFromSomewhere().getTime()));<\/pre>\n
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<\/strong> ( 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<\/a> )<\/p>\n
In BOQL by the way it would be like this:<\/p>\n
boObjectList.list(getEboContext(), \"select Object where column_data > ?\", new Object[]{ new java.sql.Date(date.getTime()) });<\/pre>\n
Important notes:<\/p>\n