Handling dates and datetimes in ServiceStack's Ormlite

Question

I am using Ormlite.Oracle to do something like this:

using (var db = dbFactory.Open())
            {
                var events = db.From<Event>()
                      .Where(row => row.Event_Date == Today);

                List<Event> results = db.Select(events);

                db.Close();
                return results;
            }

The issue is that row.Event_Date is a DateTime and Today is just a Date always ending in 00:00:00, so the where clause never matches. I would like to convert row.Event_Date to a Date. I've tried Event_Date.Date, and Event_Date.toString("MM/dd/yyyy") etc. But this doesn't work because OrmLite isn't translating these kinds of conversions very well to Oracle. For example, when I did Event_Date.toString("MM/dd/yyyy"), OrmLite translated this to a varchar(1000) cast in Oracle, but kind of forgot about passing the string format.

Is there any way to get around this without using raw sql?

Answer 1

You can simply add 1 day to Today and then use the && operator to select all Event_Date that have the correct date part without considering the time part

 var Tomorrow = Today.AddDays(1);
 var events = db.From<Event>()
                .Where(row => row.Event_Date >= Today &&
                              row.Event_Date < Tomorrow);