[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5626) hour, minute and second functions does not work on Oracle DATE field

hour, minute and second functions does not work on Oracle DATE field -------------------------------------------------------------------- Key: HHH-5626 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5626 Project: Hibernate Core Issue Type: Bug Environment: Hibernate Core 3.3.2 GA, Oracle 10g Reporter: Rodrigo Cesar Gevaerd de Faria The HQL functions hour, minute and second does not work on a column of type DATE in Oracle. The SQL generated is like: {code} extract(hour from tablename.columnName) {code} But it is not valid for such column in Oracle, causing the _ORA-30076: invalid extract field for extract source_ error. An alternative that works correctly, is using the following declarations for these functions in the dialect: {code} registerFunction("hour", new SQLFunctionTemplate(Hibernate.INTEGER, "to_number(to_char(?1, 'hh24'))")); registerFunction("minute", new SQLFunctionTemplate(Hibernate.INTEGER, "to_number(to_char(?1, 'mi'))")); registerFunction("second", new SQLFunctionTemplate(Hibernate.INTEGER, "to_number(to_char(?1, 'ss'))")); {code} This way it works on both, DATE and TIMESTAMP columns. (Note that DATE type in Oracle contains both date and time information) -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira