ObjectQuery, passing Time in Where clause

Question

How can I construct the Where clause of the ObjectQuery where I will be querying for the Time part only of a DateTime column? I've tried the following but it's not working. I get an invalid exception on Convert.

 ObjectQuery<Item> _Query = ItemEntities.CreateQuery<Item>("Item");
_Query = _Query.Where("Convert(VARCHAR,it.START_TIME,114) > '{0}'", startTime.TimeOfDay);

Also, I'm using Oracle as database. So I tried to_char instead of convert and still I get the same error.

Thanks.

Answer 1

You should be able to get the time portion from the date using TO_CHAR and then subsequently converting it to a system type e.g.

TO_DATE(TO_CHAR(it.START_TIME,'HH24:MI:SS'), 'hh24:mi:ss') from dual

If ObjectQuery doesn't support TO_CHAR you try using Extract and build up the time manually e.g.

TO_DATE(EXTRACT(HOUR FROM it.START_TIME) || ':' || EXTRACT(MINUTE FROM it.START_TIME) || ':' || EXTRACT(SECOND FROM it.START_TIME) FROM DUAL, 'HH24:MI:SS');