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

[Rodrigo Cesar Gevaerd de Faria (JIRA)]

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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira