CODEWITHSUNDEEP
hibernatehql
Sudhanshu Umalkar
Sudhanshu Umalkar

Reputation: 4202

SQL to HQL - date conversion issue

I am looking to convert the following SQL into HQL:

SELECT DISTINCT a.aid from atable a, btable b 
where a.aid=b.id and 
STR_TO_DATE(a.somecolumn, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 10 DAY AND CURDATE();

In short, we have a date stored as string in one of the columns in our table. Want to use the same in HQL to get records for a certain range.

Can this be done using a single HQL?

Upvotes: 0

Views: 1363

Answers (2)

Sudhanshu Umalkar
Sudhanshu Umalkar

Reputation: 4202

Got it working using DATEDIFF function-

SELECT DISTINCT a.aid from atable a, btable b 
where a.aid=b.id and DATEDIFF(current_date(), a.somecolumn) < 10

The date in a.somecolumn is stored in the format yyyy-MM-dd but as a string (a.somecolumn type is varchar). May need to check what other formats are supported by DATEDIFF function by default.

Upvotes: 1

swamy
swamy

Reputation: 1210

Criteria.add(Restrictions.between("propertyName","startDate", "endDate"))

Upvotes: 0

Related Questions