0

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.

spiritqueen
  • 689
  • 1
  • 6
  • 14
  • [Oracle time comparisons](http://stackoverflow.com/questions/9625323/oracle-time-comparisons)? – James May 14 '13 at 11:20
  • My problem is in the construction of the query. Since I'm using ObjectQuery to retrieve data from database, I need how to get the TIME part of it.START_TIME (which is a datetime column). I tried _Query.Where("TO_CHAR(it.START_TIME, 'hh24:mi:ss')... but I also get an exception. – spiritqueen May 14 '13 at 11:26
  • Add an exception that you're getting. – Anatolii Gabuza May 14 '13 at 11:33
  • "'to_char' cannot be resolved into a valid type or function – spiritqueen May 14 '13 at 11:38

1 Answers1

0

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');
James
  • 80,725
  • 18
  • 167
  • 237