Hibernate version: 3.2cr2 (and earlier)
Name and version of the database I am using: Sybase 12.5
HQL:
select reasonCode, sum(credit), sum(debet) from PLog
group by hour(timestamp)
The generated SQL:
select
plog0_.REASON_CODE as col_0_0_,
sum(plog0_.CREDIT) as col_1_0_,
sum(plog0_.DEBET) as col_2_0_
from
P_LOG plog0_
group by
extract(hour
from
plog0_.TIMESTAMP)
-----------------
I think this is an error in Sybase dialect. Following SQL is not recognized as valid by jdbc driver (I have tried both JConnect 5.5 and jtds 1.2):

extract(hour from plog0_.TIMESTAMP)

Parse error is "Incorrect syntax near the keyword 'from'". Although this is valid syntax according to standards, Sybase (at least 12.5 and earlier) does not support it and I have found no mention of 'extract' in Sybase's documentation.

Usual workaround for extracting date components (one that I have seen before) is to use a hack with conversing date to text format and selecting required part of that text. For example hour can be selected as

convert(char(2), timestampField, 8)

In my opinion this bug and it affects HQL functions second(...), minute(...), hour(...), day(...), month(...), year(...).

Should I submit a bug report or did I miss something?



PS Funnily enough that http://sqlzoo.net offers following SQL in the same syntax for selecting date components:

SELECT EXTRACT(YEAR FROM wk),
EXTRACT(MONTH FROM wk),
EXTRACT(DAY FROM wk)
FROM totp WHERE song='Rio'

And in turn fails to execute it with the same error :)