Saving and Querying a Date in GORM Grails

Question

I have a database table TableA, which has a column 'theDate' for which the datatype in the database is DATE.

When I save a java.util.Date to 'theDate' through GORM it appears to save just the date value when I look at the data in the table by just executing select * from TableA.

However, when I run a query such as:

select * from TableA where theDate = :myDate

No results are found, but if I run something like;

select * from TableA where theDate <= :myDate

I do get results.

So it's like the Time is relevant.

My question is how do I save a Date and query for a Date ignoring the Time completely and just matching on an exact Date only?

Thanks.

note: I have also tried using sql.Date and util.Calendar but to no success.

Answer 1

clearTime()

You can use clearTime() before saving and before comparing to zero out the time fields:

// zero the time when saving
new MyDomain(theDate: new Date().clearTime()).save()

// zero the target time before comparing
def now = new Date().clearTime()
MyDomain.findAll('SELECT * FROM MyDomain WHERE theDate = :myDate', [myDate: now])

joda-time plugin

An alternative would be to install the joda-time plugin and use the LocalDate type (which only holds date information, no times) instead of Date. For what it's worth, I don't think I've worked on a project with dates without using the Joda plugin. It's completely worth it.

Answer 2

If you have date saved without clearing you could retrieve it using range, as Jordan H. wrote but in more simple way.

def getResults(Date date) {

    def from = date.clearTime()
    def to = from + 1

    def results = MyDomain.findAll("from MyDomain where dateCreated between :start and :stop" ,[start:from,stop:to])

}