hql query to get records of the week

[krish bhadragiri]

hi,
i want to retrieve records of the week (i.e records since last 7days).i wrote
"from WorkDetails as work_details where work_details.dated<=current_date and dated>=currrent_date-7 and work_details.employee_id="+employee_column_id"

but its throwing:org.hibernate.exception.SQLGrammarException:could not execute query.

[Paul Sturrock]

How do you set current_date? Should you not be using the current_date() function?

[krish bhadragiri]

actually m setting the dates using string date format as '2007-04-19'in dated field of mysql table of colum type date.

so i want querries for retrieving records for last seven days and last 1 month.

[Jeanne Boyarsky]

Since this is a Hibernate question, I'm moving it to our Object/Relational forum.

[David Madouros]

This feels like more a hack than anything, but...

The problem is that mysql converts dates to numerics when you add or subtract. For example, current_date = 2007-04-20, but (current_date + 0) = 20070420. A solution (hack) for this is to do the following:

...where work_details.dated <= current_date and (work_details.dated + 0) >= (current_date - 7)

Ugly, but it works.