Why Hibernate HSQL Concat is not working for MSSQL?

Question

So, I have Hibernate 5.3.1 in a project which connects to different enginees (MySql, Oracle, PostgreSQL and MS SQL), so I can't use native queries.

Let's say I have 3 records in a table, which all of them have the same datetime, but I need to group them only by date (not time). For example, 2019-12-04;

I execute this query:

SELECT 
 CONCAT(year(tx.date_), month(tx.date_), day(tx.date_)),
 iss.code,
 COUNT(tx.id) 
FROM 
 tx_ tx 
JOIN 
 issuer_ iss 
ON 
 tx.id_issuer = iss.id  
GROUP BY 
 CONCAT(year(tx.date_), month(tx.date_), day(tx.date_)), iss.code

But, when I test it connected to SQL SERVER 2017, instead of return 20191204, it's returning 2035. In Oracle and MySQL is working fine.

Anyone has any idea why is this happen? I've tried different ways, like use + instead of CONCAT but the result is the same.

I've also tried to extract them for separate (without concat), and they have been returning correct. The problem is, I need to group them by the complete date.

And just for the record, the field is declared as datetime2 in DDBB

Answer 1

Thanks for the hint Gert Arnold gave me. I just didn't realize that the query was adding like if they were numbers in MSSQL.

Finally, I manage to make it work in the 4 RDBMS casting to string first

SELECT 
 CONCAT(CAST(year(tx.date_) AS string), CAST(month(tx.date_) AS string), CAST(day(tx.date_) AS string)),
 iss.code 
FROM 
 tx_ tx 
JOIN 
 issuer_ iss 
ON 
 tx.id_issuer = iss.id  
GROUP BY 
 CONCAT(year(tx.date_), month(tx.date_), day(tx.date_)), iss.code

I tried also casting to TEXT, but it throws exception in MySQL