Leo Ribeiro Leo Ribeiro - 4 months ago 57
Java Question

Hibernate + SQL Server - Cast Timestamp to Date error

I'm using Springboot 1.3.5.RELEASE in my project, and SpringDataJpa 1.9.4.RELEASE, Hibernate 4.3.11.FINAL, to access a MSSQL Server 2014. In my application.properties I set the most recent dialect which is org.hibernate.dialect.SQLServer2012Dialect and I'm using the Microsoft JDBC Driver com.microsoft.sqlserver.jdbc.SQLServerDriver, that I imported manually (sqljdbc4.jar).

The problem is, I want to retrieve a simple query using my createdAt column which is a Timestamp to cast as Date (YYYY-MM-DD). So I have the below named query in my Transaction entity:

SELECT new myproject.wrapper.TrendingChartData(cast(createdAt as date),
liveVerifyCode, count(*))
FROM Transaction t
WHERE t.bucket.id = :bucketId


GROUP BY cast(createdAt as date), liveVerifyCode

But my retrieve date are something like:

DATE | Code | Count
2016-06-10| 2 | 1
2016-06-10| 1 | 1
2016-06-10| 2 | 1
2016-06-10| 1 | 1
2016-06-10| 2 | 1
2016-06-10| 2 | 1
2016-06-10| 1 | 1
2016-06-10| 1 | 1
2016-06-10| 1 | 1


So it appears that it's casting the result but it's casting the grouping. I have generated a trace of my SQL and I have:

select cast(transactio0_.createdAt as datetime) as col_0_0_,
transactio0_.liveVerifyCode as col_1_0_,
count(*) as col_2_0_
from TransactionData transactio0_
where transactio0_.bucket_id=?


group by cast(transactio0_.createdAt as datetime), transactio0_.liveVerifyCode

So for some reason casting datetime instead of date. If run the same above query but changing the keywords datetime to date it works perfectly in MSSQL... It appears that is something to do with Hibernate MSSQL Dialect.

Do you guys have any idea to help me?

Thank you very much!

Answer

For future folks that may have this issue, I solved this problem using the below approach:

SELECT new myproject.wrapper.TrendingChartData(
       year(t.createdAt), month(t.createdAt), day(t.createdAt),
       liveVerifyCode, count(*))  
  FROM Transaction t 
 WHERE t.bucket.id = :bucketId 
 GROUP BY year(t.createdAt), month(t.createdAt), day(t.createdAt), t.liveVerifyCode
 ORDER BY year(t.createdAt), month(t.createdAt), day(t.createdAt)

Thank you!