I have an application involving booking people into shift work. The shifts have a date, start time, and end time. I store this information in two timstamps start and end. Timstamps so that I can store date and time together in one field hence easily accomodating shifts that span midnight, and leaving comaprison and time based arithemetic working correctly for reporting etc.

Some of the summary screens in the application require listing of various shifts that fall (or start on) particular days, but the time part of the start and end doesn't matter. On these screen I need to sort by start DATE (not time) and then subsort by some other field. To do this I want to:

'order by cast(shift.start as date), shift.otherfield'

Simply sorting without casting won't work as the subsort will be by time rather than the other field. Can I do this sql casting with hibernate? Would I need to drop down to a sql query?

many thanks
markj

you can call SQL functions in the HQL order by clause