SQL Server, HQL: How to compare SQL server datetime column field to date

Question

I have following SQL query which i need to make it work in HQL, I tried couple of solutions but none of them work.(Database : SQL server)

select t.createdTimeStamp FROM ServiceData t , UserInfo C where C.UserId = t.UserId and  CAST(t.createdTimeStamp AS DATE) = '2016-05-30'

I tried following in HQL but none worked, (createdTimeStamp is of datetime datatype in sql server )

Query:

select t.createdTimeStamp FROM ServiceData t , UserInfo C where C.UserId = t.UserId and  CAST(t.createdTimeStamp AS DATE) = '2016-05-30'

Error:

org.hibernate.QueryException: Could not resolve requested type for CAST : DATE

any suggestion on how to make it work would be helpful.

Have you tried casting the date parameter? `select t.createdTimeStamp FROM ServiceData t , UserInfo C where C.UserId = t.UserId and CAST(t.createdTimeStamp AS DATE) = CAST('2016-05-30' as date)` — vercelli, Jun 08 '16 at 09:49
@vercelli I tried it, got below error **" java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode [IDENT] IdentNode: 'createdTimeStamp' {originalText=createdTimeStamp} ] with root cause java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode "** — Chandra_S, Jun 08 '16 at 10:02
maybe this would help you: http://stackoverflow.com/questions/26471534/no-data-type-for-node-org-hibernate-hql-internal-ast-tree-identnode-hql — vercelli, Jun 08 '16 at 10:04
@vercelli, Thanks for your suggestions, I found help in below URL.[StackOverflow](http://stackoverflow.com/questions/20973272/date-is-not-a-recognized-built-in-function-name) — Chandra_S, Jun 08 '16 at 11:29

score 12 · Answer 1 · answered May 05 '17 at 20:07

12

you have to write the cast command as followed, then it will work

cast(t.createdTimeStamp as date)

so "cast", "as" and "date" in small caps

answered May 05 '17 at 20:07

JFSolution

1

Only `date` needs to be in lowercase - https://blogs.sentryone.com/aaronbertrand/backtobasics-lower-case-data-types/ – Jezor Apr 09 '18 at 10:13

score 2 · Accepted Answer · edited May 23 '17 at 12:02

2

I used CONVERT instead of CAST and it worked

Query:select t.createdTimeStamp FROM ServiceData t , UserInfo C where C.UserId = t.UserId and CONVERT(date,t.createdTimeStamp) = '2016-05-30'

but not sure why CAST didn't work, as per Hibernate Doc CAST is supported

edited May 23 '17 at 12:02

Community

answered Jun 08 '16 at 12:50

Chandra_S

2 Answers2