[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2967) Cast to date in Formula still doesn't work

[Frederic Leitenberger (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28940 ] 

Frederic Leitenberger commented on HHH-2967:
--------------------------------------------

I found a workaround:

-- Create another function
create or replace function trunc_date(timestamp_ in timestamp) return date as
begin
	return trunc(cast(timestamp_ as date));
end;
/

-- Use this in the Forumla (avoiding the cast):
@Formula(value = "trunc_date(created)")
    public Date getDate() {
        return date
    } 

This doesn't work with overloading, since the datatypes in oracle seem to be overloaded themselfe and the original function is called instead of my new function.
Unless one knows the exact datatypes to use it won't work with overloading (namely using the "trunc" name for the function).

> Cast to date in Formula still doesn't work
> ------------------------------------------
>
>                 Key: HHH-2967
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-criteria, query-hql, query-sql
>    Affects Versions: 3.2.5
>         Environment: Hibernate-Version: 3.2.5.ga
> Oracle 9i/10g
>            Reporter: Frederic Leitenberger
>            Priority: Minor
>
> Similar to HHH-473.
> Oracle 10g supports trunc(TS). (TS is a timestamp)
> Oracle 9i only supports trunc(cast(TS as date)) [and 10g supports this still too].
> Therefore i need to add the cast to the Forumla.
>     @Basic
>     @Column(nullable = false, updatable = false)
>     @Temporal(TemporalType.TIMESTAMP)
>     public Date getCreated() {
>         return created;
>     }
>     public void setCreated(Date created) {
>         this.created = created;
>     }
>     @Formula(value = "trunc(cast(created as date))")
>     public Date getDate() {
>         return date
>     }
>     public void setDate(Date date {
>         this.date= date
>     }
> This results in the following query:
> select
>         eventlog0_.id as id9_0_,
>         eventlog0_.created as created9_0_,
>         .........
>         trunc(cast(eventlog0_.created as eventlog0_.date)) as formula6_0_
>     from
>         ICCS6.EventLog eventlog0_
>     where
>         eventlog0_.id=?
> The alias in front of "date" is obviously misplaced there.
> I also tried renaming the formula (since it was called "date"), but this didn't change the result.

-- 
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