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

[Michael Pätzold (JIRA)]

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

Michael Pätzold commented on HHH-2967:
--------------------------------------

Hi,

the same problem is existing in SQLServer SQLs, i.e.:

> CONVERT(datetime, fieldXyz, 103)

is converted to

> CONVERT (this_.datetime, this_.fieldXyz, 103)

-----------

A very very ugly workaround is using...
> CONVERT([datetime], fieldXyz, 103)
in combination with specifying MySQLDialect.java.

Somehow the expression is not converted to [this_.datetime]. But when I use SQLServerDialect, the brackets will be removed and this_. will be added again.

Why does the converter not recognize the key words?

> Cast to date in Formula still doesn't work
> ------------------------------------------
>
>                 Key: HHH-2967
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2967
>             Project: Hibernate Core
>          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
>         Attachments: testcase.jar
>
>
> 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