[FIXED] Hibernate + SQL Server - Cast Timestamp to Date error

Issue

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!

Solution

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!

Answered By - Leo Ribeiro